Select Distinct but want lower ID

[rodeomount]

I have the following query which will return 2 records (Each has different dtPrinted values but the same intTblID values):

Select intTblID * from tblMyTable

How can I modify the query below to return the record with the oldest dtPrinted value

Select DISTINCT intTblID * from tblMyTable

 

[gmmastros]

Select intTblId, Min(dtPrinted) As OldestValue
From   tblMyTable
Group By intTblId

If this works for you, and you would like me to explain it, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Hopefully this will help:

SELECT DISTINCT ClaimID,
	[OldestDate] = MIN(TransactionDate),
	[NewestDate] = MAX(TransactionDate)
FROM TransactionDetail 
GROUP BY ClaimID

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[gmmastros]

ousoonerjoe,

You do NOT need to use distinct in your query. Since you are grouping by ClaimId, it will be distinct anyway. Distinct is an expensive (think time consuming) operation for the database. In your example query, the optimizer may decide that it's unnecessary and remove it auto-magically for you. But, if the records are going to be distinct anyway, why leave it up to the optimizer.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLSister]

sooner joe, you should not in general use distinct with group by. Group by will already give you the distinct rows and distinct just makes the query less efficient.

"NOTHING is more important in a database than integrity." ESquared

 

[rodeomount]

I made a mistake. I meant to post the following query:

Select intTblID from tblMyTable

and


Select DISTINCT intTblID from tblMyTable.

Will the following work as well?

Select intTblID from tblMyTable Order By dtPrinted

 

[gmmastros]

Try this...

Select Top 1 intTblId from tblMyTable Order By dtPrinted



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ousoonerjoe]

Thanks.

I guess it never hit me that GROUP BY was a DISTINCT in and of itself. I've had to make some pretty nasty pulls (possibly self inflicted at times) that required it so the DISTINCT wasn't exactly obvious.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ESquared]

Sometimes, though rarely, DISTINCT is called for in a GROUP BY query, but only when the GROUP BY list is longer than the selected columns list.

However, to my surprise I recently found situations where doing another GROUP BY with the original query in a derived table had a far superior execution plan to using DISTINCT. This is not the exact same thing as given in this thread but it illustrates how different the handling of DISTINCT vs. GROUP BY can be. This is from a denormalized documents/pages table (it's a work table to analyze data problems and will be deleted when the analysis is finished).

SELECT
   DocName,
   DocumentCount = Count(DISTINCT DocGroup)
   PageCount = Count(*)
FROM Pages
GROUP BY DocName

SELECT
   DocName,
   DocumentCount = Count(*),
   PageCount = Sum(Pages)
FROM (
   SELECT DocName, DocGroup, Pages = Count(*)
   FROM Pages
   GROUP BY DocName, DocGroup
) X
GROUP BY DocName

These both give the same result set. The interesting thing is that the first query cannot have a WITH ROLLUP clause. Try it. You'll get an error. So I had to do the second one to get my WITH ROLLUP in there. Then the execution plan showed that the first query had two long parallel streams that had to be joined. The second query was a single stream: much more efficient.