Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
Finding last in a sequence with SQL |
Fri, Apr 10 2009 5:15 AM | Permanent Link |
adam | Hi Folks,
Say I have data like this: ID Name TestResult 1 Bill 20 2 Bill 22 3 Bill 34 4 Jim 11 5 Jim 14 6 Jim 25 I want to pick out the _last_entered_ results for Bill and Jim. ID increments automatically, so the MAX ID for Bill and Jim will be their last test result. However SQL: SELECT MAX(ID), Name, TestResult FROM TestResults GROUP BY Name Gives the following result: 3 Bill 20 6 Jim 11 i.e. it returns the test results for tests 1 and 4, not 3 and 6. -- I realise I can get the result with a second query to create a memory table in the form: SELECT MAX(ID) as ID, Name INTO MEMORY\MaxTests FROM TestResults GROUP BY ID ; SELECT ID, Name, TestResult FROM TestResults WHERE ID IN (SELECT ID FROM MEMORY\MaxTests) -- This works, but am I missing an easier way of writing it? Or ... is there a better way to think about the data in the first place? |
Fri, Apr 10 2009 8:50 AM | Permanent Link |
"Robert" | "adam" <adam@fullwellmill.co.uk> wrote in message news:26D45989-779E-4F69-939B-89F9205EE91A@news.elevatesoft.com... > Hi Folks, > > Say I have data like this: > > ID Name TestResult > 1 Bill 20 > 2 Bill 22 > 3 Bill 34 > 4 Jim 11 > 5 Jim 14 > 6 Jim 25 > > I want to pick out the _last_entered_ results for Bill and Jim. ID > increments > automatically, so the MAX ID for Bill and Jim will be their last test > result. > > However SQL: > > SELECT MAX(ID), Name, TestResult > > FROM TestResults > GROUP BY Name > > Gives the following result: > > 3 Bill 20 > 6 Jim 11 > > i.e. it returns the test results for tests 1 and 4, not 3 and 6. > > -- > > I realise I can get the result with a second query to create a memory > table in the form: > > SELECT MAX(ID) as ID, Name > > INTO MEMORY\MaxTests > > FROM TestResults > > GROUP BY ID <<<<????? > > ; > > SELECT ID, Name, TestResult > > FROM TestResults > > WHERE ID IN > > (SELECT ID FROM MEMORY\MaxTests) > > > -- > > This works, but am I missing an easier way of writing it? > Or ... is there a better way to think about the data in the first place? > Even if you get it to work on a single query, there is no guarantee it will always work in the future. You should only select the aggregate fields and the group by fields in the query. That's it. Also, your GROUP BY is wrong. Try this SELECT MAX(ID) as MID, Name INTO MEMORY\MaxTests FROM TestResults GROUP BY Name; CREATE INDEX ByID ON Memory\MaxTests (MID); SELECT ID, Name, TestResult FROM TestResults JOIN Memory\MaxTests ON (MID = ID); Why do you duplicate the name field in all rows? Robert |
This web page was last updated on Tuesday, May 7, 2024 at 06:25 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |