SQL query

[sanket044]

hey i am having three columns "fname", "mname", "lname" in one table...

i want to take each column's DISTINCT value without relating fields in one query..

i mean distinct of "fname" in first column, distinct of "mname" in second column and distinct of "lname" in third column

can anyone help me to write that query...

[poojavb]

try giving distinct keyword for all the three columns....

[sanket044]

no its also not working...

[adam_k]

I don't have SQL now to provide the actual query, but you should be able to use ROW_NUMBER() as a key for joining subqueries (ie select distinct fname, row_number() over (order by fname) from table)
Remember to outer join, so you won't limit your results to the smallest count of the 3 columns.
If you need the actual query, let me know and I'll provide.

[G_Waddell]

No sure what you are trying to do, do you what a result that lists each first name then each second name used by the first name then each last name used by the combination of both or do you just want a list of distinct Firstnames, Middlenames, lastnames in no particular order?

[G_Waddell]

SELECT DISTINCT Fname as Name From MyTable
UNION
SELECT DISTINCT MName AS Name FROM MyTable
UNION
SELECT DISTINCT LName As Name FROM MyTable

[adam_k]

@G_Waddell: This should give you a hint:

i mean distinct of "fname" in first column, distinct of "mname" in second column and distinct of "lname" in third column

UNION will give you all results in the same column.

[sanket044]

G_Waddell : your query is giving all results in one column, but i want them in seperate columns...

[sanket044]

adam_k: ya if you can provide that query then that might help me...

[adam_k]

I've used my employee's table and since I don't have middle name I've used username as the third field.
Please note a couple things:
a) you can't select distinct and row_number in the same select, as row_number will make each record distinct. You have to use distinct in a subquery and apply row_number to the results of that subquery.
b) If you inner or left/right join you risk eliminating records. Inner join will only return as many rows as your subquery with the fewer records. Left joining (all 3 subqueries) will return as many rows as the subquery in the leftmost position and similarly with right joining. Instead you use full outer join to get all records.
c) If you don't include or b.row = c.row and make sure that is OR and not AND then you will screw up the results since username row in this example won't match the lastname row and you will get usernames with row > max(a.row) after all lastnames have been listed. If you use 'AND' then you won't get usernames (or middle names in your case) with row > max(a.row) and max(b.row)

select firstname, lastname, username
from 
(select firstname, row_number() over (order by firstname) as 'row'
from (select distinct firstname from employee) a ) a
full outer join 
(select lastname, row_number() over (order by  lastname) as 'row' from 
(select distinct lastname from employee) b ) b
on a.row = b.row 
full outer join 
(select username, row_number() over (order by  username) as 'row' from 
(select distinct username from employee) c ) c
on a.row = c.row
or b.row = c.row

Let me know if you need additional info.

[M.Waqas Aslam]

well you can do like this , as in my computer there is no mssql installed so may be this will not work properly but give you an idea.

 select distinct fname ,'','' from table
      union all 
 select distinct '',mname,'' from table
  union all 
 select distinct '','',lname from table

this is just an idea try it . may be this will solve your prob :)

Best Regards

[adam_k]

Can you imagine the member top list (http://www.daniweb.com/stats/toplist/) being coded with all those empty cells?

[M.Waqas Aslam]

adam_k i cant get your poin , please can you explain it.

[adam_k]

Your query will return (I'll use NULL instead of '' to make it apparent):
fname1,NULL,NULL
fname2,NULL,NULL
NULL,mname1,NULL
NULL,mname2,NULL
NULL,mname3,NULL
NULL,NULL,lname1
NULL,NULL,lname2

I believe that the desired output is:
fname1, mname1,lname1
fname2,mname2,lname2
NULL,mname3,NULL

Similarily the members top list has 3 lists with top members by different metric. If it was written the way you are suggesting then we would get 3 times as much records, with 2 columns blanks at any given time.
I'm not suggesting that the page is written using something similar to my query, but I'm using it as a visual reference to what is my understanding of the request.

[G_Waddell]

Sorry,
I still don't see what the point of this is or what you are trying to do.

For example, say you have the following six users:

John Steven Smith
Billy Bob Jones
John Bill Smith
Fred Bob Roberts
Joe Tom Bloggs
Tom Bill Stevens

You want (assuming you list alphabetically):
Billy Bill Bloggs
Fred Bob Jones
Joe Steven Roberts
John Tom Smith
Tom NULL Stevens

[sanket044]

adam i tried your query but it gives error that syntax error near FROM clause.

[M.Waqas Aslam]

hye try this .hope this time it works fine :P

select distinct fname , (select distinct mname from table1) as mname , (select distinct lname from table1) as lname
from table1

Regards