swemaniac


Hi

Say I have a table with columns 'id, date, title, text' and in order to display a list of 'month year' I need to select rows with distinct month & year of the date component.

I can select distinct year & month components by doing this:

select distinct convert(varchar(7), date, 111) as date from posts

but how do I return the other columns as well

Regards,
Johan




Re: Select full rows depending on distinct date

Robert Rossney


I think you're misunderstanding what DISTINCT does. If you have three rows with the same month/year, the select you have above will return one row. If each row contains a different title, and you add title to the select list, the select will return three rows. If two of the three rows contained the same title, it would return two rows.

What are you trying to accomplish, exactly





Re: Select full rows depending on distinct date

swemaniac

Maybe I am.. It just seemed like the logical approach. Let me clear things up:
I just want to return one of each of the rows that share the same month & year in the date column. Make sense

id title date
1 bill 6/7/2006
2 gus 14/7/2006
3 dan 6/8/2007

I want two rows back - one of 1 and 2 and number 3.

Regards,
Johan






Re: Select full rows depending on distinct date

DaleJ

Code Snippet

set dateformat dmy

create table #t (id tinyint, title varchar(10), date datetime)

insert into #t

select 1, 'bill', '6/7/2006'

union all select 2, 'gus', '14/7/2006'

union all select 3, 'dan', '6/8/2007'

select left(convert( varchar(10), date, 111 ), 7) as AsOf,

substring( ( SELECT ', ' + convert( varchar(10), id ) + '-' + title AS [text()]

FROM #t t2

WHERE left(convert( varchar(10), t1.date, 111 ), 7) = left(convert( varchar(10), t2.date, 111 ), 7)

FOR XML path(''), elements ), 2, 1000) as data

from #t t1

group by left(convert( varchar(10), date, 111 ), 7)






Re: Select full rows depending on distinct date

swemaniac

Thanks Dale, however that won't return the actual rows (as 'id, title, date'), that will give me 'date, id+title'..





Re: Select full rows depending on distinct date

DaleJ

OK.

Your desired results were a little unclear.

You said: "One of 1 and 2..."

Do you really want "one of 1 OR 2 "

If so, if there are multiples of a given month/year, how do you want to determine which one to keep.






Re: Select full rows depending on distinct date

swemaniac

On a second thought I'm gonna mark your suggestion as the answer, since I can still make it work. Thanks!





Re: Select full rows depending on distinct date

DaleJ

Thanks!

If you have any more issues, just let us know.