Select DISTINCT with JOIN? - 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 2005 Forums

Transact-SQL (2005)

Select DISTINCT with JOIN?

Author

Topic

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-12 : 18:06:20

I'm trying to get the following results from my query, but I cannot figure out how to get the DISTINCT "f_analyticdatetime" as well as the DISTINCT "f_itemname".

quote:


145	01/04/10	Boston Market
139	02/08/10	Boston Market
155	02/09/10	Applebees
136	02/09/10	Boston Market
168	02/09/10	Maritime Museum
161	02/09/10	Yang Sing

I only need one "f_itemname" for each day (day conversion is done in SQL from datetime).

My query looks like this, and gives me the following results:

quote:


SELECT an.f_analyticID, convert(varchar, an.f_analyticdatetime, 1) AS f_analyticdatetime, ISNULL(re.f_itemname, at.f_itemname) AS f_itemname 
FROM tb_analytics an 
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID 
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID 
ORDER BY f_analyticdatetime ASC, f_itemname ASC

quote:


145	01/04/10	Boston Market
139	02/08/10	Boston Market
140	02/08/10	Boston Market
155	02/09/10	Applebees
156	02/09/10	Applebees
157	02/09/10	Applebees
143	02/09/10	Boston Market
144	02/09/10	Boston Market
146	02/09/10	Boston Market
142	02/09/10	Boston Market
168	02/09/10	Maritime Museum
158	02/09/10	Maritime Museum
161	02/09/10	Yang Sing
162	02/09/10	Yang Sing

I have tried all variations of DISTINCT that I can find on Google with no luck. My best efforts result in "Incorrect syntax near the keyword 'DISTINCT'."

Thanks in advance,
Matt

P.S. - Sorry for all the crazy questions today. I'm trying to train myself on T-SQL through real-world examples. Most of this stuff is pretty basic, but now that I'm getting into JOINS and other combinations of pivots. My stress level is through the roof at times, but I'm picking it up.

Thanks,
Matt

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-12 : 23:57:01

[code]
try this once
SELECT f_analyticID,f_analyticdatetime,f_itemname
FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY ISNULL(re.f_itemname, at.f_itemname),convert(varchar, an.f_analyticdatetime, 1)
ORDER BY an.f_analyticID)AS rid,
an.f_analyticID, convert(varchar, an.f_analyticdatetime, 1) AS f_analyticdatetime,
ISNULL(re.f_itemname, at.f_itemname) AS f_itemname
FROM tb_analytics an
LEFT JOIN tb_restaurants re ON an.f_analyticassignment = re.f_itemGUID
LEFT JOIN tb_attractionsamenities at ON an.f_analyticassignment = at.f_itemGUID )S
WHERE rid = 1
ORDER BY f_analyticdatetime ASC, f_itemname ASC
[/code]

mattboy_slim
Yak Posting Veteran

72 Posts

Posted - 2010-02-15 : 10:04:59

Wow, that works but I'm not sure I understand it. Can you please elaborate on what this is doing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 10:38:07

quote:
Originally posted by mattboy_slim

Wow, that works but I'm not sure I understand it. Can you please elaborate on what this is doing?

it justs groups the result based on value of two fields represented by ISNULL(re.f_itemname, at.f_itemname),convert(varchar, an.f_analyticdatetime, 1) and then numbers them sequentially as 1,2,3... based on order specified by ORDER BY which is an.f_analyticID. then it returns only the ones having rid = 1. So in effect what you do is to retrieve only record with minimum f_analyticID value within each group of f_itemname,f_analyticdatetime

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources