Antonio Fernández


Hello,

I'm newbie at sql, Im trying to get results from a query getting the last state an issue is. This are my 2 tables:

Table 1 : issues

id_issue

name_issue

data_issue

Table 2: hist_issues

id_hist

id_issue (have relation with issues.id_issue 1-to-many)

state

who

date_time

Well, Im using hist for save all the states where the issue is (received, in process and closed). There is a one to many relation with that 2 tables. The thing I want is make a select that returns me the data of the last state where the issue is.

I tryed with a select distinct but it use the id of the hist_issues and I need that use the id of issues table, and take the one with date next to present.

Code Snippet
SELECT DISTINCT issues.id_issue, issues.name_issue, issues.data_issue, hist_issues.id_hist, hist_issues.state, hist_issues.who, hist_issues.date_time FROM issues INNER JOIN hist_issues ON issues.id_issue = hist_issues.id_issue;

Thanks in advance.





Re: Selecting distinct rows, please help me with the statement

rh4m1ll3


try this if your hist_issues.id_hist is unique or an identity column

SELECT issues.id_issue
, issues.name_issue
, issues.data_issue
, hist_issues.id_hist
, hist_issues.state
, hist_issues.who
, hist_issues.date_time
FROM issues INNER JOIN
hist_issues ON issues.id_issue = hist_issues.id_issue INNER JOIN
(
SELECT id_issue
, MAX(id_hist) AS id_hist
FROM hist_issues
GROUP BY
id_issue
) mx ON hist_issues.id_issue = mx.id_issue
AND issues.id_issue = mx.id_issue
AND hist_issues.id_hist = mx.id_hist;

the query above by the way returns only the issues with at least 1 history, just change the INNER JOIN on mx to a LEFT OUTER JOIN


if your hist_issues.id_hist is not unique, use the hist_issue.date_time instead

SELECT issues.id_issue
, issues.name_issue
, issues.data_issue
, hist_issues.id_hist
, hist_issues.state
, hist_issues.who
, hist_issues.date_time
FROM issues INNER JOIN
hist_issues ON issues.id_issue = hist_issues.id_issue INNER JOIN
(
SELECT id_issue
, MAX(date_time) AS date_time
FROM hist_issues
GROUP BY
id_issue
) mx ON hist_issues.id_issue = mx.id_issue
AND issues.id_issue = mx.id_issue;
AND hist_issues.date_time = mx.date_time;





Re: Selecting distinct rows, please help me with the statement

Ennor

Something like this, I suppose:

Code Snippet

select *
from issues i
inner join hist_issues h on i.id_issue = h.id_issue
where not exists (
select * from hist_issues where id_issue = h.id_issue and date_time > h.date_time
)


Not very effective from performance perspective, though.





Re: Selecting distinct rows, please help me with the statement

Arnie Rowland

I think that this will work for you, and be reasonable efficient performance-wise too.

Code Snippet


SET NOCOUNT ON


DECLARE @Issues table
( ID_Issue int IDENTITY,
Name_Issue varchar(20),
Data_Issue varchar(20)
)


DECLARE @Hist_Issues table
( ID_Hist int IDENTITY,
ID_Issue int,
State char(1) CHECK ( State IN ( 'R', 'P', 'C' )),
Who varchar(25),
Date_Time datetime
)


INSERT INTO @Issues VALUES ( 'Bug', 'BadData' )
INSERT INTO @Issues VALUES ( 'GUI', 'Spelling' )
INSERT INTO @Issues VALUES ( 'ShortCutKey', '[CTRL][F11]' )


INSERT INTO @Hist_Issues VALUES ( 1, 'R', 'Bill', '2007/01/01' )
INSERT INTO @Hist_Issues VALUES ( 3, 'R', 'Sally', '2007/02/11' )
INSERT INTO @Hist_Issues VALUES ( 2, 'R', 'Fred', '2007/03/15' )
INSERT INTO @Hist_Issues VALUES ( 3, 'P', 'Bob', '2007/02/25' )
INSERT INTO @Hist_Issues VALUES ( 3, 'C', 'Bill', '2007/03/03' )
INSERT INTO @Hist_Issues VALUES ( 2, 'P', 'Sally', '2007/04/12' )


SELECT DISTINCT
i.ID_Issue,
i.Name_Issue,
i.Data_Issue,
h.ID_Hist,
h.State,
h.Who,
Date_Time = cast( h.Date_Time AS char(11))
FROM @Issues i
JOIN (SELECT
ID_Issue,
ID_Hist = max( ID_Hist )
FROM @Hist_Issues
GROUP BY ID_Issue
) dt
ON dt.ID_Issue = i.ID_Issue
JOIN @Hist_Issues h
ON dt.ID_Hist = h.ID_Hist



ID_Issue Name_Issue Data_Issue ID_Hist State Who Date_Time
----------- ------------ ------------ ----------- ----- ---------- -----------
1 Bug BadData 1 R Bill Jan 1 2007
2 GUI Spelling 6 P Sally Apr 12 2007
3 ShortCutKey [CTRL][F11] 5 C Bill Mar 3 2007