sql server query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

sql server query

Author

Topic

Dev@nlkss

134 Posts

Posted - 2009-04-24 : 05:48:21

I have following data

S 3
S 3
S 3
S 3
S 3
B 2
C 2
C 2
C 2
M 1
M 1
M 1
A 4
A 4
A 4

how do i display as follows
S 3
C 2
M 1
A 4

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 05:53:48

SELECT DISTINCT Co1, Col2
FROM Table1

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 05:54:20

B 2

is missing from your expected output?

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2009-04-24 : 06:13:41

Thanks for reply
The actual data is as follows
S 3
S 3
S 3
S 3
S 3
C 2
C 2
C 2
C 2
M 1
M 1
M 1
A 4
A 4
A 4

sorry it was not B 2 its C 2 .

SELECT DISTINCT Co1, Col2
FROM Table1
if i execute above query the order will cahnge.
I want exact order as my output like

S 3
C 2
M 1
A 4

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 06:51:03

Please do tell us which constitutes the order of the records in your table.

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2009-04-24 : 07:10:56

Sorry If i am not clear.
The alphabets are under column NAME and numerics are under VALUE.
S --> 3
C --> 2
M --> 1
A --> 4
The output should be as above order.
because VALUE 3 contains sortID as 1 as same for all.

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 07:19:52

Where is the SORTID column?

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2009-04-24 : 07:35:53

NAME - VALUE - SortID
S 3 1
S 3 2
S 3 3
S 3 4
S 3 5
C 2 6
C 2 7
C 2 8
C 2 9
M 1 10
M 1 11
M 1 12
A 4 13
A 4 14
A 4 15

as above Value 3 contains Sortid as 1 thus it should come first and under VALUE 3 there are 5 items as sortids from 1-->5 then 6th item starts for 2nd value thus it should come second and so on...
As i require only NAME And VALUE Columns so i can have either min or max from each group but order must be same.

$atya.

Love All Serve All.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-24 : 07:39:20

Now, FINALLY, we have enough information to proceed.

SELECT Name, Value
FROM Table1
GROUP BY Name, Value
ORDER BY MIN(SortID)

Now, this wasn't so hard, was it? To provide enought information so that a solution is possible.

E 12°55'05.63"
N 56°04'39.26"

Dev@nlkss

134 Posts

Posted - 2009-04-24 : 07:49:28

Thanks.
The same way i did but i dont know where i have gone wrong.
any how i got solution.
Thanks again.

$atya.

Love All Serve All.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources