problem with select distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

problem with select distinct

Author

Topic

freq
Starting Member

17 Posts

Posted - 2007-10-15 : 12:26:59

I'm having problems with the below select statement..

select distinct LogProject, LoginDate from tblCheckIn where VolunteerID=" & VolID & " order by LoginDate

It's not giving me unique LogProjects.

But if I remove LoginDate and use the below select statement, it seems to work..
select distinct LogProject from tblCheckIn where VolunteerID=" & VolID & "

Problem is, I need the LoginDate. Is there something wrong with the syntax?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-15 : 12:37:38

Provide some sample data and expected output. I am guessing you need to use a co-related query.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-15 : 12:38:54

Nothing wrong with the syntax - the problem is with the thinking.
You have several different dates for a project - what would you expect the server to retrun for the single row?
try
select LogProject, max(LoginDate) from tblCheckIn where VolunteerID=" & VolID & " group by LoginDate order by LoginDate

and maybe try the access forum?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

freq
Starting Member

17 Posts

Posted - 2007-10-15 : 13:04:40

I'm not looking for a single row. What I'm looking for is multiple rows--but each row should have a unique LogProject.
I'll try the access forum also, thanks.

quote:
Originally posted by nr

Nothing wrong with the syntax - the problem is with the thinking.
You have several different dates for a project - what would you expect the server to retrun for the single row?
try
select LogProject, max(LoginDate) from tblCheckIn where VolunteerID=" & VolID & " group by LoginDate order by LoginDate

and maybe try the access forum?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 13:16:00

If you have data of


LogProject LogDate
1001       01-Jan-2007
1001       02-Jan-2007
1002       03-Feb-2007
1002       04-Feb-2007

and you want output of:
[code]
LogProject LogDate
1001 ???
1002 ???
[code]
what should appear for the LogDate? It is either a single value for the LogProject (e.g. the MIN(LogDate) or you will have multiple rows with the same LogProject.

Kristen

freq
Starting Member

17 Posts

Posted - 2007-10-15 : 13:58:30

So my select statement is incorrect?
What I want is rows containing unique LogProject values, and I also want the LogDates associated with those LogProjects.

quote:
Originally posted by Kristen

If you have data of

LogProject LogDate
1001 01-Jan-2007
1001 02-Jan-2007
1002 03-Feb-2007
1002 04-Feb-2007

and you want output of:
[code]
LogProject LogDate
1001 ???
1002 ???
[code]
what should appear for the LogDate? It is either a single value for the LogProject (e.g. the MIN(LogDate) or you will have multiple rows with the same LogProject.

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 14:58:07

"I also want the LogDates associated with those LogProjects."

Yes but HOW? If there are multiple LogDates for a given LogProject HOW can you display ONLY ONE ROW for a LogDate, as you have said you want?

You said:

"each row should have a unique LogProject."

Show some sample data, and sample output, of what you want to do please

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 03:17:43

Did you try uning nr's query?

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-16 : 08:53:17

Read my post again - a bit more carefully.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 09:14:14

Thank you, I understand what you're saying about how distinct works.
I tried nr's suggestion and got the error - 'order by clause conflicts with distinct'.
I removed the order by clause and got the error - 'You tried to execute an expression that does not include the specified expression 'LogProject' as part of an aggregate function'

quote:
Originally posted by nr

Read my post again - a bit more carefully.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 09:17:07

Nr was right on track, but misspelled some columns.

Try this:
select LogProject, max(LoginDate) from tblCheckIn where VolunteerID = " & VolID & " group by LogProject order by LogProject

E 12°55'05.25"
N 56°04'39.16"

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 11:03:33

Using Peso's suggestion, it still throws the aggregate expression error.

quote:
Originally posted by Peso

Nr was right on track, but misspelled some columns.

Try this:
select LogProject, max(LoginDate) from tblCheckIn where VolunteerID = " & VolID & " group by LogProject order by LogProject

E 12°55'05.25"
N 56°04'39.16"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-10-17 : 11:12:25

Are you sure the error is coming from exectuting the sql? How are you trying to execute it?
Try it with a hard coded value so

select LogProject, LoginDate = max(LoginDate) from tblCheckIn where VolunteerID = 1 group by LogProject order by LogProject

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 11:44:37

I'm executing from VBA in access.
Same error using nr's most recent suggestion.

quote:
Originally posted by nr

Are you sure the error is coming from exectuting the sql? How are you trying to execute it?
Try it with a hard coded value so

select LogProject, LoginDate = max(LoginDate) from tblCheckIn where VolunteerID = 1 group by LogProject order by LogProject

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-17 : 11:51:31

HOW are you executing this query?

E 12°55'05.25"
N 56°04'39.16"

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 13:13:38

How?? You'll have to be more specific.
Here's some sample code ..
strSQL = "select LogProject, LoginDate=max(LoginDate) from prjCheckIn where LogInVolunteer=1"
Set rsCheckIn = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

quote:
Originally posted by Peso

HOW are you executing this query?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 13:33:55

"I'm executing from VBA in access"

Is the data stored in Access or SQL Server?

Kristen

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 13:54:24

Access.

quote:
Originally posted by Kristen

"I'm executing from VBA in access"

Is the data stored in Access or SQL Server?

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 14:28:51

Well this is the SQL Server (2000) Development forum ... so answers given here may not work on Access!

freq
Starting Member

17 Posts

Posted - 2007-10-17 : 15:48:57

Your answers have been very helpful in pointing me in the right direction, and my cross-post in the Access forum has little activity.

quote:
Originally posted by Kristen

Well this is the SQL Server (2000) Development forum ... so answers given here may not work on Access!

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 16:07:17

"my cross-post in the Access forum has little activity"

yeah, but this is very specifically a Microsoft SQL Server forum and we've proven numerous times in the past that we don't know what we are talking about on other matters!

At the very least it is important that folk know this is an Access problem, and not a SQL Server problem - despite the Forum it is posted in.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources