quick question on select

[crystal]

let say my table is this.

part1 name1
part1 name2
part2 name3
part2 name4
part3 name1


what select sql statement return the result set of
part1 name1/name2
part2 name3/name4
part3 name1
In another word - I don't care what "name" return if a part contains more than 1 name.

 

[Crusty]

You're missing a lot of key details. When asking database questions you should really post a table schema dump and what query you are using that won't work.

From the looks of it you've got a table that isn't normalized, but what's the difference between part1 and name1? Is one a number and one a string? What is being used as criteria for the query?

 

[Atheus]

You want a random selection of the name?

 

[crystal]

basically what I want is unique return of all the part. select distinct column1 from table_x (said table is my sample table up there.) will give me 3 row return. part1, part2, and part3.

but if I do select distinct column1, column2 from table_x - I will get all rows listed up there return. What I want is only 3 row return, and don't care what "name" ties to a given part. The name is needed, but it could be name1 or name2 for example.

 

[brandonbull]

select column1, max(column2) from table_x group by column1

 

[crystal]

Originally posted by: brandonbull
select column1, max(column2) from table_x group by column1

thx. ^ that would work. I though I have to do something crazy like

select x2.column1, (select colunm2 from table_x x1 where x1.column1 = x2.column1 and rownum=1) column2
from table_x x2

 

[drebo]

I seriously suggest you reconsider your data model.

 

[Crusty]

Originally posted by: drebo
I seriously suggest you reconsider your data model.

:thumbsup:

 

[Evadman]

Originally posted by: drebo
I seriously suggest you reconsider your data model.

holy crap, no joke.

 

[crystal]

Originally posted by: Evadman

Originally posted by: drebo
I seriously suggest you reconsider your data model.

holy crap, no joke.

hehe, that example is a view where we got read access only and the view contains over 20 columns. The sql for the view queries data from 8 or so tables. I guess they afraid us bring the db down if they give us read access to those table directly. They might be right.