Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 6th, 2005, 05:37 PM
Registered User
 
Join Date: Sep 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default sorting duplicates in group by

I am looking for the best way to retrieve unique records using two fields as criteria for the duplicates. I have a table with xID and yID fields (along with a timestamp and primary id) and have been using a group by on the xID and yID which results in what I want....except, I want the latest item. It gives me the earlier item, even though I order by timestamp desc...see below

SELECT primaryID
FROM table
GROUP BY xID, yID
ORDER BY timestamp DESC

This is simplified, but is basically what I want. But I am getting no effect either way on the order by. I am using mySQL 4.

I am assuming group by doesn't allow this much control? I am using group by instead of distinct because I am selecting a bunch of other fields as well in my actual query. Maybe I should be using a subselect with a distinct?

This has always been a little gray for me...

Thanks for any help!

 
Old September 7th, 2005, 01:30 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

You need to work this from the "inside out".

Here's one way to do this. There are probably others.

Suppose you had a table in your database ('GroupTable') that contained the xID, yID, and maximum Timestamp values for each xID, yID combination.

This could be created by:
Code:
    INSERT GroupTable
    SELECT xID, yID, MAX(timestamp) as MaxDate
    FROM table
    GROUP BY xID, yID;
If this table actually did exist, you could JOIN your table to it and select
the primaryID for each resultant row:
Code:
SELECT primaryID
FROM table T1
INNER JOIN GroupTable T2
ON T1.xID = T2.xID
AND T1.yID = T2.yID
AND T1.Timestamp = T2.MaxDate;
In your case the GroupTable doesn't exist, but you can always materialize such a table by using a subquery:
Code:
SELECT primaryID
FROM table T1
INNER JOIN 
    (SELECT xID, yID, MAX(timestamp) as maxdate
    FROM table
    GROUP BY xID, yID) T2
ON T1.xID = T2.xID
AND T1.yID = T2.yID
AND T1.Timestamp = T2.MaxDate;
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com




Similar Threads
Thread Thread Starter Forum Replies Last Post
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
Select min(price) Group By sku = duplicates Stuart Stalker SQL Server DTS 2 March 6th, 2006 01:36 AM
need to Dynamically group sorting ??? kspiderman Crystal Reports 3 May 24th, 2005 03:55 PM
Sorting based on formula in group footer NixyJ Crystal Reports 0 August 27th, 2004 06:37 AM
Datagrid sorting by non alphabetical sorting? LLAndy VS.NET 2002/2003 1 July 15th, 2004 01:20 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.