By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

Select all the distinct rows with a condition.

P: 1
It may be simple for many of you. But I am stuggling with this.

I have a simple table like this. I want to select the records with one record per each name which has recent date. the result should be like given below.

NAME DATE
---- ----
NAME1 2008-02-12 06:32:14.380
NAME2 2004-03-14 06:35:03.420
NAME3 2002-07-20 06:23:21.463
NAME2 2001-03-01 04:33:24.356
NAME1 1999-03-14 02:35:04.410
NAME2 2005-01-10 06:55:42.430
NAME2 2002-09-09 03:22:27.378
NAME3 2003-03-30 06:11:13.120
NAME3 2001-04-10 06:03:45.454

RESULT
------
NAME1 2008-02-12 06:32:14.380
NAME2 2005-01-10 06:55:42.430
NAME3 2003-03-30 06:11:13.120


How can I achieve this?
Mar 10 '08 #1
Share this Question
Share on Google+
13 Replies


amitpatel66
Expert 100+
P: 2,367
It may be simple for many of you. But I am stuggling with this.

I have a simple table like this. I want to select the records with one record per each name which has recent date. the result should be like given below.

NAME DATE
---- ----
NAME1 2008-02-12 06:32:14.380
NAME2 2004-03-14 06:35:03.420
NAME3 2002-07-20 06:23:21.463
NAME2 2001-03-01 04:33:24.356
NAME1 1999-03-14 02:35:04.410
NAME2 2005-01-10 06:55:42.430
NAME2 2002-09-09 03:22:27.378
NAME3 2003-03-30 06:11:13.120
NAME3 2001-04-10 06:03:45.454

RESULT
------
NAME1 2008-02-12 06:32:14.380
NAME2 2005-01-10 06:55:42.430
NAME3 2003-03-30 06:11:13.120


How can I achieve this?
Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT name,MAX(date) FROM table1 GROUP BY date
  3.  
  4.  
Mar 10 '08 #2

natalie99
P: 41
Hello

I have the same requirements as the original query, only my date column format is as such:

JAN08
MAR07
NOV06
AUG07

Is it possible to use an SQL similar to your solution rather than having a date table which assigns value to each of the month&year combinations?

Thanks very much!
Mar 11 '08 #3

amitpatel66
Expert 100+
P: 2,367
Hello

I have the same requirements as the original query, only my date column format is as such:

JAN08
MAR07
NOV06
AUG07

Is it possible to use an SQL similar to your solution rather than having a date table which assigns value to each of the month&year combinations?

Thanks very much!
Try using DATEPART function to achieve your results.
Mar 11 '08 #4

natalie99
P: 41
Hey thanks!

I tried the DATEPART function but it didn't seem to work, it must be a user error :)

I do, however, have this code working, the value comes from a table giving each month/year combo an autonumber.......

SELECT qryBATCH_val.[CIRCUIT ID], Max(qryBATCH_val.Value) AS MaxOfValue
FROM qryBATCH_val
GROUP BY qryBATCH_val.[CIRCUIT ID];

but now I can't add other fields from qryBATCH_val

do you know why?? Is it because I am using totals rows?

help would be very much appreciated please :)

thanks

nat
Mar 11 '08 #5

amitpatel66
Expert 100+
P: 2,367
Hey thanks!

I tried the DATEPART function but it didn't seem to work, it must be a user error :)

I do, however, have this code working, the value comes from a table giving each month/year combo an autonumber.......

SELECT qryBATCH_val.[CIRCUIT ID], Max(qryBATCH_val.Value) AS MaxOfValue
FROM qryBATCH_val
GROUP BY qryBATCH_val.[CIRCUIT ID];

but now I can't add other fields from qryBATCH_val

do you know why?? Is it because I am using totals rows?

help would be very much appreciated please :)

thanks

nat
YES you cannot becuase if you add any more column in SELECT clause then you need to include that coulmn in GROUP BY clause as well to make your query work. Try it this way and post back in case of any errors.
Mar 11 '08 #6

natalie99
P: 41
hey again

I can make the query run if I add the other columns into the Group By statement, however.....

I only want one row per ID, and that one row is only to be determined by the most recent date i.e. MAX(value)

when I add group by to other fields I end up with more records than there should be! why is this? and is it possible to fix it?

thanks again!

nat
Mar 11 '08 #7

amitpatel66
Expert 100+
P: 2,367
Try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT x.Cd,x.MaxOfValue,y.<yourcolumnname1>,y.,yourcolumnname2> FROM
  3. (SELECT qryBATCH_val.[CIRCUIT ID] AS Cd, Max(qryBATCH_val.Value) AS MaxOfValue
  4. FROM qryBATCH_val
  5. GROUP BY qryBATCH_val.[CIRCUIT ID]) x, qryBATCH_val y
  6. WHERE x.Cd = y.[CIRCUIT ID]
  7. AND x.MaxOfValue = y.Value
  8.  
  9.  
Mar 11 '08 #8

natalie99
P: 41
OK

SELECT x.Cd, x.MaxOfValue, y.[SUBSCRIBER NUMBER], y.[SUBSCRIBER COUNTRY]
FROM [SELECT qryBATCH_val.[CIRCUIT ID] AS Cd, Max(qryBATCH_val.Value) AS MaxOfValue
FROM qryBATCH_val
GROUP BY qryBATCH_val.[CIRCUIT ID]]. AS x, qryBATCH_val AS y
WHERE x.Cd = y.[CIRCUIT ID]
AND x.MaxOfValue = y.Value;


I'm still getting duplicates :(

Any ideas?

Thanks for your help & time also!
Mar 11 '08 #9

amitpatel66
Expert 100+
P: 2,367
OK

SELECT x.Cd, x.MaxOfValue, y.[SUBSCRIBER NUMBER], y.[SUBSCRIBER COUNTRY]
FROM [SELECT qryBATCH_val.[CIRCUIT ID] AS Cd, Max(qryBATCH_val.Value) AS MaxOfValue
FROM qryBATCH_val
GROUP BY qryBATCH_val.[CIRCUIT ID]]. AS x, qryBATCH_val AS y
WHERE x.Cd = y.[CIRCUIT ID]
AND x.MaxOfValue = y.Value;


I'm still getting duplicates :(

Any ideas?

Thanks for your help & time also!
Could you please post the sample data and the output of the query that I have provided so that I can find out what is the problem. There should be some problem with the data.
Mar 11 '08 #10

natalie99
P: 41
er, um, the sample data is 65,000 rows :)

I'll find specific doubles and post them as examples, will that help?

thank you so much!
Mar 11 '08 #11

amitpatel66
Expert 100+
P: 2,367
er, um, the sample data is 65,000 rows :)

I'll find specific doubles and post them as examples, will that help?

thank you so much!
Post the data for particular CIRCUIT ID (all the columns that are selected in the query) for which they are duplicating.
Mar 11 '08 #12

natalie99
P: 41
:(

I found my error, as you suggested it is a data problem, I have attached the reasoning, could you please shed some light on my best course of action??

the top section is the base data, ie. qryBATCH_val

the bottom section is the resultant duplicates from your SQL query all due to data as far as I can see :)

thanks again, file attached!

data file
Mar 11 '08 #13

amitpatel66
Expert 100+
P: 2,367
:(

I found my error, as you suggested it is a data problem, I have attached the reasoning, could you please shed some light on my best course of action??

the top section is the base data, ie. qryBATCH_val

the bottom section is the resultant duplicates from your SQL query all due to data as far as I can see :)

thanks again, file attached!

data file
I am not able to view the attached file because of access rights. Could you please post the data directly here for reference.
Mar 12 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.