Return all fields with SELECT DISTINCT

[Shrum]

I have a database that has all my site content in it, each page broken down into various components. I want to generate a listing of the distinctive TYPEs of entries that are in the database.

I've tried something like...

SELECT DISTINCT Type FROM datasource

...but this only returns the TYPE field (not to mention wierd results...I only have about 15 distinct TYPEs...this returns 71 out of 117?!?). The reason I need the complete record is that I use a templating scheme and do not want to make "special-case" templates.

To recap, I want to list 1 complete record for each distinct TYPE.

Thanks to all those who have helped me out and the past and TIA.

Sean Shrum

http://www.shrum.net

 

[Shrum]

Opps...never mind...I had additional query statements present. I have self-corrected.

"Couldn't see the forest through the trees."

 

[Shrum]

Okay, I now have a correct number of records returned but my original issue still stands...

The query is only returning 1 field: TYPE.

How do I structure the SELECT to display all fields?

TIA

 

[Shrum]

ACK!!!! This ALMOST works, HELP!

SELECT Distinct Type,* FROM datasource WHERE Area='Family'ORDER BY Type asc

While I have all my fields displayed, the field order is now offset by one due to TYPE being first, not to mention this query returns additional records (DISTINCT operations on '*'?).

How do I:

1) return ALL fields, and
2) retain field order, while
3) returning those records where TYPE is DISTINCT?

Sorry for all the posts...I've been up too late trying to hack this together.

TIA

 

[NVSbe]

Assuming your table has an id field

select * from datasource
where id in (
select max(id)
from datasource
where Area = 'Family'
group by type);

The inner query returns the highest id for each type, and the outer query fetches the data for those id values. It should work. --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[Shrum]

Um...I'm not looking for highest ID...I need Distinct TYPE but I need all fields returned.

:-( Where did you get the idea that I needed max(ID)???

TA.

 

[NVSbe]

Well, I figured seeing you're wanting distinct type, you wanted only one record of each type... So I figured if only one record of each type is wanted, we'll get the highest id numberin that type and show that...

Suppose the table is like this:

ID TYPE
-- ----
1 A
2 A
3 B
4 C

The result of the query would be

ID TYPE
-- ----
2 A
3 B
4 C

That's not what you wanted? --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[Shrum]

Ah...a data sample would have probably been helpful.

OK. Here's the data set:

ID|Type|Area|Hits
1|Article|Family|12
2|Datasheet|Family|1
3|Article|Family|15
4|Article|Programming|12
5|Whitepaper|Programming|20
6|Photos|Family|3

Scenario: From the dataset above, I need a SQL string with a distinct call for all TYPEs of records that have 'Family' in AREA. The result should produce:

ID|Type|Area|Hits
1|Article|Family|12
2|Datasheet|Family|1
6|Photos|Family|3

I've been banging my head on this for too long...it's 4am. I'm off to bed. Please let me know if you have any ideas.

TIA

 

[NVSbe]

Use my query, it'll work. If you want the first record of a certain type, replace max() by min()

Let me go in detail as to what my query does:

This is the dataset it begins with:

ID|Type|Area|Hits
1|Article|Family|12
2|Datasheet|Family|1
3|Article|Family|15
4|Article|Programming|12
5|Whitepaper|Programming|20
6|Photos|Family|3

Now, the inner query will sort this by type (for Family)

ID|Type
1|Article
3|Article
2|Datasheet
6|Photos

Then group it by type... this means only one record per type will be shown. You need to use min or max here on the
id here,because there are more than one possible id's, and you only want one. Ifyou use max, this is the recordset the inner query produces

ID|Type
3|Article
2|Datasheet
6|Photos

Now, the outer query will look up all the data from the id's in the inner query, so you'll get, eventually:

ID|Type|Area|Hits
2|Datasheet|Family|1
3|Article|Family|15
6|Photos|Family|3


Which is what you want, right?
--------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.

 

[r937]

>> "Where did you get the idea that I needed max(ID)???"

shrum, it was constructed from your statement that you wanted all fields returned, which means entire rows, yet you also wanted to restrict which rows, by a condition that only one row of each type be selected

therefore it is necessary to have some criterion to select, from amongst all the rows of each type, only one of them, and a very convenient method for choosing amongst them, is to pick the one with the highest ID

in the example you gave, where you start out with a scenario of four 'Family' rows:

1|Article|Family|12
2|Datasheet|Family|1
3|Article|Family|15
6|Photos|Family|3

and then you go on to say that the "distinctness" of Type should produce:

1|Article|Family|12
2|Datasheet|Family|1
6|Photos|Family|3

i would like to point out that there was a group there -- Article, with two rows -- and you happened to choose one row out of that group, namely the row with MIN(ID)

does NVSbe's explanation make more sense now?

rudy

 

[Shrum]

Ah...I see.

I never thought of using min or max. I was thinking it was going to be more like how you set up the SELECT portion of the SQL statement.

Thanks.

 

[NVSbe]

That's my problem... I usually know how to construct the sql well, but have a hard time putting it in readable english.. Aah, the curse of being a programmer --------------------------------------
It's not the monsters under your bed, it is the men next door.
That make you fear, make you cry. Make you cry for the Child.
All the wars are fought amongst those lonely men. Unharmed, unscarred.