Tough SQL statement 6

[levinll]

I'm using the below data and I'm trying to write some SQL code so for each uniqueHeader_ID in the table, I only get those records that have the latest date in the EditDate field. For example, for Header_ID #66963 I'd want to get back rows 6,7,8,9, and 10. For Header_ID #66964 I'd want to get back rows 11,12,13,14, and 15.

Can this be done ?

ID Header_ID EditDate
1 66963 10/28/2002 12:43:55 PM
2 66963 10/28/2002 12:43:55 PM
3 66963 10/28/2002 12:43:55 PM
4 66963 10/28/2002 12:43:55 PM
5 66963 10/28/2002 12:43:55 PM
6 66963 10/28/2002 12:47:04 PM
11 66964 10/28/2002 1:18:26 PM
13 66964 10/28/2002 1:18:26 PM
15 66964 10/28/2002 1:18:26 PM
7 66963 10/28/2002 12:47:04 PM
8 66963 10/28/2002 12:47:04 PM
9 66963 10/28/2002 12:47:04 PM
10 66963 10/28/2002 12:47:04 PM
12 66964 10/28/2002 1:18:26 PM
14 66964 10/28/2002 1:18:26 PM

 

[SQLBill]

Try this:

SELECT
id
, DISTINCT header_id
, max(editdate)
FROM
mytable


-SQLBill

 

[levinll]

That only returns one record per Header_Id.I had to add an order caluse in order for it to work.

This is what I tried based on your suggestion.

SELECT DISTINCT Header_ID, max(editdate)
FROM dbo_WeekHistory Group by Header_ID;

 

[SQLBill]

Great! I'm glad to hear that you got it to work. Sometimes you have to take what we post and experiment with it to get it to do what you actually want it to do. I wasn't able to test the script before I posted it (I don't have a test database).

-SQLBill

 

[r937]

>> SELECT DISTINCT Header_ID, max(editdate)
>> FROM dbo_WeekHistory Group by Header_ID

this is okay, except the DISTINCT is redundant

all groups are distinct by definition of grouping

i'm not sure whether sql/server would actually do DISTINCT testing anyway, or just ignore it

note that DISTINCT applies to all the columns in the SELECT list, not just the first one (a common misconception)


rudy

 

[levinll]

Sorry for the premature celebration. When I said I had to add an "order by" clause in order for the code you suggested to work I meant the code you suggested errored out when I tried it.

Adding the "order by" clause only returned one row for each Header_ID, not all the rows that I wanted.

 

[SQLBill]

Like I said, I can't test this but give it a try and let me know what happens:

SELECT
header_id
, editdate
FROM
dbo.weekhistory
GROUP BY
header_id
HAVING
editdate = max(editdate)

-SQLBill

 

[sunila7]

Hi,

Try this Query......

Select * from mytable t1
Inner Join (SELECT header_id , max(editdate)
FROM
mytable Group by header_id) tbl1 on tbl.headerid=t1.headerid and t1.editdate = tbl1.editdate


hope it helps...........
Sunil

 

[levinll]

No go on your last suggestion. I get a SQL error:
"You tried to execute a query that does not include the specified expression 'editdate' as part of an aggregate fuction.

If you give me your email address, I'll send you an access table you can use that has the sample data in it.

 

[sunila7]

Hi,

Made a mistke in previous query....

Select * from mytable t1
Inner Join (SELECT header_id , max(editdate) editdate
FROM
mytable Group by header_id) tbl1 on tbl.headerid=t1.headerid and t1.editdate = tbl1.editdate


Hope it helps....

Sunil

 

[r937]

bill, i'd be truly surprised if that runs on sql/server

GROUP BY must mention all non-aggregate fields in the SELECT list, and HAVING editdate = max(editdate) would be invalid because it contains a non-aggregate

levinll, try this --

select ID
     , Header_ID
     , EditDate
  from yourtable  XX
 where EditDate =
       ( select max(EditDate)
           from yourtable
          where Header_ID = XX.Header_ID )

this is a correlated subquery and is more understandable (to me, anyway) than the other way of doing it, with a join

rudy

 

[levinll]

We have a winner !!!!!

Thanks everyone !!

 

[sunila7]

Hi,

I was looking at the execution plan for both methods and i found that if u use the join method it will work much faster than the corelated subquery....


hope it helps.......


Sunil

 

[SQLBill]

Rudy,

Thanks....I forgot to put editdate in the GROUP BY. I was actually trying to get what you came up with, but I was stuck on comparing the same table to itself (the sub-select's where), I forgot about table alias'. It's been a long day.

Gave you a STAR!

-SQLBill

 

[r937]

thanks bill

and i had to give sunil a star too, for checking the execution plans

i love correlated subqueries but if the join runs faster...

rudy

 

[sunila7]

Hi rudy,

Thanks for the star.... i was doing some more research on the issue.... i created an index on the date field... to check whether that makes a difference on how the query executes, still the query with join worked faster... i guess it is b'cos in ur query a look up has to be done based on the date....

Sunil

 

[levinll]

OOps. Just realized I need the earliest date, not the latest date. I tried replacing Max(EditDate) with Min(Editdate), but I don't get any records back.

What am I doing wrong ???

 

[r937]

can we see the query that's not working?

 

[levinll]

select * from GetEditedProcessedRecords AS XX where XX.WeekEnding < @bdate and XX.EditDate between @bdate and @edate and ((((XX.EditDate)=(select min(EditDate) from GetEditedProcessedRecords where Header_ID=XX.Header_ID))))

 

[sunila7]

Hi,

Does this query work....


Select * from mytable t1
Inner Join (SELECT header_id , min(editdate) editdate
FROM
mytable Group by header_id) tbl1 on tbl.headerid=t1.headerid and t1.editdate = tbl1.editdate

Hope it helps

Sunil