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

Transact-SQL (2000)

select distinct

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-04 : 12:53:23

Here's my sql

select distinct holiday,holidaylink,holidayid from holidays

now I really want the distinct holiday but not according to holidaylink and holidayid (I only want those to go in the query results)

but I want the holiday to be unique entries.

how can i do this?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 13:47:02

select holiday,holidaylink,holidayid from holidays h
where holiday in (Select distinct holiday from holidays dh )

or u may use an left join

select holiday,holidaylink,holidayid from holidays h
Left Join (Select distinct holiday from holidays ) dh on h.holiday = dh.holiday

Srinika

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-04 : 14:03:38

ok so i tried

select holiday,holidaylink,holidayid from holidays where date2006=#4/14/2006# and holidaylink in (Select distinct holidaylink from holidays)

but it still doesn't work and I imagine it's because it the holidaylink is distint in the in query but appears for 2 records from the holiday table. (meaning thre are 2 records with the same holidaylink so I want it to only appar once)
What can I do to fix?

the second join also returns an error -- the specified field holiday refers to more then one field in the from clause.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 14:10:38

For ur concern 1 : Post some data and expected results

For ur concern 2 : Each field should be preceded with the table name (or alias in this case)
eg. Select h.holiday, h.holidaylink ....

Srinika

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-04 : 14:26:06

ok

data is
holidayid
holiday
holidaylink
date2006

examples

now in many cases you can have a holiday like newyears that appears in the db twice -- I want the query to only pull up one record for those that appear twice.

is it clear waht i need?

meaning if data has

goodfriday
newyears
goodfriday
kingday

then when I query it it should only retunr in the results
goodfriday
newyears
kingday

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-04 : 14:36:24

esthera,

I do not have time to interpret ur data
Post some data

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 17:45:25

Is this what you want ?

select h.*
from   holidays h
inner join
(
    select holiday, min(holidayid) as m_holidayid
    from   holidays
    group by holiday
) m
on  h.holiday   = m.holiday
and h.holidayid = m.m_holidayid

If not try to post your table structure, some sample data and the required result as in http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-05 : 03:47:04

no my data is such that I have more then one holiday with teh same holidaylink -- I want the query to only pull up one holiday in this case. the id and all other inforamtion might be different but if the holidaylink is in teh db more then once then I want to pull up just teh first record with that holiday link. Now I need to show more fields besides the holiday link.

this is an access db so I can't easily create teh ddl but talbe is

holidayid (autonumber)
holiday (text)
holidaylink (text)
date2006 (date field)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 04:18:41

Did you try Tan's code?
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-05 : 04:28:39

no it doesn't seem right

sample data is

1 neweyars newyears.asp
2 goodfriday goodfriday.asp
3 goodfridayorthodox goodfridayorthodox.asp
4 goodfriday goodfriday.asp
5 memorial day memorialday.asp

now in the above the results should return (not goodfriday should only be shown once even though in twice)

1 neweyars newyears.asp
2 goodfriday goodfriday.asp
3 goodfridayorthodox goodfridayorthodox.asp
5 memorial day memorialday.asp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-05 : 04:35:54

Isn't my script gives you the result that you want ?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-04-05 : 04:43:25

let me look at it again and play aroudn with data... maybe I just didn't understand it properly.
thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-05 : 04:48:26

Also try

Select * from yourtable
where id in (select min(id) from yourTable group by holiday,holidaylink)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources