Getting Distinct and * - 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

General SQL Server Forums

New to SQL Server Programming

Getting Distinct and *

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-24 : 09:30:21

How do I get all of the columns from the table and the distinct date with this query?

SELECT DISTINCT CONVERT(varchar(10), Date, 101) AS Friday_Date
FROM test
WHERE (DATEPART(dw, Date) = 6)
ORDER BY Friday_date

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 10:13:09

quick and dirty:
select * from test
where CONVERT(varchar(10), Date, 101)
in
(
SELECT DISTINCT CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test WHERE (DATEPART(dw, Date) = 6)
)

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-24 : 10:58:17

Thanks I got the results but I see I need to add more to the query.

I need to add to the distinct clause and get these fields as well:
Office, Area, loccode, dist and date. I tried this but got this error message:

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Do I need to add the fields I want instead of the having * listed (the other fields I want are called CO, CR, Ans

select * from test
where CONVERT(varchar(10), Date, 101)
in
(
SELECT DISTINCT Office, Area, Loccode, dist, CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test WHERE (DATEPART(dw, Date) = 6)
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 11:08:25

Yes I think so.
SELECT DISTINCT Office, Area, Loccode, dist, CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test
where CONVERT(varchar(10), Date, 101)
in
(
SELECT DISTINCT CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test WHERE (DATEPART(dw, Date) = 6)
)

No, you're never too old to Yak'n'Roll if you're too young to die.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-24 : 11:18:59

Thanks that did it!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-24 : 11:21:45

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-25 : 01:23:11

You dont need distinct in subquery as outer has it

SELECT DISTINCT Office, Area, Loccode, dist, CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test
where CONVERT(varchar(10), Date, 101)
in
(
SELECT CONVERT(varchar(10), Date, 101) AS Friday_Date FROM test WHERE (DATEPART(dw, Date) = 6)
)

Madhivanan

Failing to plan is Planning to fail

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-11-25 : 09:17:07

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources