DISTINCT

[monkeytooth]

Is is there a way to get all row values from using DISTINCT on a single column? right now I am using a bit that looks like:

SELECT DISTINCT colname FROM tablename

 

Now that works as far as I can tell getting the specific column and getting the distinct values I need from the one column but I need multiple items returned from each row that the distinct value is on without it pulling distinct values where both are distinct but one or the other could be the same.

 

What I have is a table with 450,000 records on it. col1 is what I am saying i need the distinct from but col1, col2 and col3 are the values I would like to return per distinct col1 is this at all possible?

[PFMaBiSmAd]

I need multiple items returned from each row that the distinct value is on

 

Which row having the same distinct col1 value do you want the other values from? The first matching row, the last, the third from the top?

 

I suspect that you don't want to use DISTINCT at all. Could you post an example showing data and the expected output?

[monkeytooth]

Ok 

 

My Table rough Example

Table Name: school
Structure:
schooladdress | schoolname | schoolstate | comments | rating | info

... theres about 10 more columns attached to this table but for the sake of attempting to stay on point I wont list them all  this database originally was not built by me so I am trying to work with what I got.

 

What I am ultimately trying to do is dump into a new table, specifics from this table. Main reason why is because what I am trying to pull out as distinct from this bigger table of 450k records is any one of 1500 or so unique/distinct items. Where these items are multiplied into the thousands per unique value due to the way who ever built this built this. The other reason is I am trying to expand the databases for services we are building up that are specific to why I want to dump these certain values.

 

I posted my sample code prior as to what I am using to call out the records as distinct its nothing elaborate but it was working for the cause per say as I only needed one value prior..

 

What I need to output now is not just the schoolname but schoolstate and schooladdress

[monkeytooth]

Ok, I think I solved my issue here. But wanted to see if anyone has any opinion as to maybe a faster way of doing it.

 

I changed my original

SELECT DISTINCT schoolname FROM schools

 

To

SELECT DISTINCT schoolname, schoolstate, schooladdress FROM schools GROUP BY schoolname

 

And that seems to have worked, allowing me to pull more info than just the one columns worth of data that the original allowed.