SELECT DISTINCT

[Anon]

How do I return more than one column when using SELECT DISTINCT, without the extra columns being included in the 'DISTINCT' part?

eg.

SELECT DISTINCT col1, col2, col3 FROM table

Checks col2 and col3 are distinct too, whereas I just want distinct col1's!

I'm using MS SQL Server.

[[deleted]]

Your request is illogical.

DISTINCT will look at all possible values for a column and report each unique value once.

Now you say you also want to print the value of another column, but there may be a thousand different records that have the same value for the 'distinct' column, so how is SQL supposed to know which one you want?

[Anon]

I was thinking something along the lines of:

SELECT DISTINCT(some_key) some_key, some_value FROM table

Where some_key is unique, but some_value ain't!

I can do it by using

SELECT DISTINCT some_key FROM table

and then running a seperate sql query as I loop round the results, but that's quite inefficient.

[Anon]

DOH! I know what you mean, sorry!!

I guess I'll have to rethink this and come back... :o)

[Anon]

Ok, here's the problem...

I'm generating a timesheet, which links a "people" table to a "timesheet" table as follows:

people

people_key
name

timesheet

timesheet_key
people_fkey
timesheetdate
numhours

Now I'm writing the script that draws the timesheet for a given week. It does a left join from people to timesheet, where timesheetdate is within the given week range OR where timesheetdate is null. This means if they have no data in the timesheet table, it'll still pull them out, using the null record created using the LEFT join.

I then loop around the returned results outputting the weeks hours (moving to the next record when I hit each day, or if that day doesn't exist, it just outputs no hours and tries the next one)...

HOWEVER... If a person has some data in the timesheets table for a week other than the given week, it doesn't create the null record from the LEFT join, as it has data. These rows are then dropped because they don't match the date, so this person doesn't end up in the resulting rows, and therefore doesn't appear on the timesheet.

Any ideas? Feel free to ask questions.

[[deleted]]

So you want to list timesheets per person for a given week, but also list people that have not timesheets for that week?

Why don't you just use two queries, one to find all the people who do have timesheets for that week, and another to find all the people who don't?

[Anon]

That would change the order per-week.

I've managed to do it now, it's a long-winded way, so I won't go into explaining it unless anyone's really curiors.

Thanks for your time,

Daniel Tuppeny
NewMind.co.uk

[[deleted]]

I am curious!

[gettingold]

me too!

[Anon]

Ok, firstly it's in ASP, so it's slightly different, but I guess you'll see where I'm going with it...

I selected everything from the joined tables, regardless of date, in person order, then date order. Then, I stepped through the recordset until either the date was inside of the range, or it moved on to the next person. If it moved onto the next person, I went back and just outputted an empty row for the previous person. Else, I ran a loop from 1 to 7 (for the 7 days of the week), on each one, checking if the recordset date was "today". If so, I outputted that time, and moved to the next field in the recordset, else I just went on to the next day, and left the recordset as it was (A litt enote: In ASP you can access the returned record, without causing it to move to the next record like mysql_fetch_array does). Once it completed all 7 days, it kept moving to the next record until it hit eof or the next person.

Hope that's understanable!!

Regards,

Danny Tuppeny
[e-mail]

[gettingold]

thanks danny although im sure that some smart alec will highlight a better way of doing it 🙂

main thing is it works for you, thsnks for the insight.

rob

[Anon]

Well if someone does, I'd love to see it!! :o)