problem with retveing data - 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 2008 Forums

Transact-SQL (2008)

problem with retveing data

Author

Topic

roma26ch
Starting Member

5 Posts

Posted - 2011-07-22 : 22:01:33

hi,
i have the following table :Products(item1,item2,total)
with the data:
item1 | item2 | total |
1135 | 1190 | 5 |
1190 | 1135 | 5 |
how can i get the only one record : 1135,1190,5 (or 1190,1135,5)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2011-07-23 : 00:41:14

select *
from
Products
where /*YOUR COLUMN NAME*/ item1 = 1135

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

roma26ch
Starting Member

5 Posts

Posted - 2011-07-23 : 04:46:44

ok and if i have many records like this, e.g:
item1 | item2 | total |
1135 | 1190 | 5 |
1190 | 1135 | 5 |
1120 | 1107 | 4 |
1107 | 1120 | 4 |
1200 | 1455 | 3 |
1455 | 1200 | 3 |
and i want the records : 1135, 1190 , 5
1120, 1107 , 4
1200, 1455 , 3
how go i get them?
like i said i have many "double" records like this.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-23 : 05:34:46

what is the criteria for those records that you want ?

KH
[spoiler]Time is always against us[/spoiler]

roma26ch
Starting Member

5 Posts

Posted - 2011-07-23 : 05:39:50

i am not sure i understand the question.. all my records in the table appear in this way("double")
i need a SELECT query that returns me all the records without "double" values.

lets say that a "double" value look like this
1135,1190,5
1190,1135,5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-23 : 05:45:00

[code]

;with cte as
(
select *, row_no = row_number() over (partition by case when item1 < item2 then item1 else item2 end, case when item1 > item2 then item1 else item2 end, total
order by item1)
from tbl t1
)
select *
from cte
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

roma26ch
Starting Member

5 Posts

Posted - 2011-07-23 : 05:51:52

can you explain the code please, i am not sure i understood it right....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-23 : 06:05:11

Firstly, read up the BOL on row_number() over ( partition by . . . order by . .)

The query uses row_number() to numbered the rows.

For rows in same group like 1135,1190,5 & 1190,1135,5, the numbering will be restart from 1

To determine which rows are of same "group", that's what the "case when" is doing.

the idea is to PARTITION BY <lower value> , <higher value>, total

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-23 : 06:08:40

try changing the row_no to


row_no = row_number() over (partition by item1, item2, total order by item1)

and see the value of row_no in the result and compare with the query that i posted

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-23 : 06:47:00

[code]
SELECT t1.item1,t1.item2,t1.total
FROM Table t1
LEFT JOIN Table t2
ON t2.Item1 = t1.Item2
AND t2.Item2 = t1.Item1
AND t2.total = t1.total
WHERE t1.Item1 <= t1.Item2
GROUP BY t1.item1,t1.item2,t1.total
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

roma26ch
Starting Member

5 Posts

Posted - 2011-07-23 : 07:27:53

quote:
Originally posted by visakh16

SELECT t1.item1,t1.item2,t1.total
FROM Table t1
LEFT JOIN Table t2
ON t2.Item1 = t1.Item2
AND t2.Item2 = t1.Item1
AND t2.total = t1.total
WHERE t1.Item1 <= t1.Item2
GROUP BY t1.item1,t1.item2,t1.total

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

thanks! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-23 : 07:51:53

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources