Click here to Skip to main content
15,917,061 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
i have two table : Tb1,Tb2


TB1 
------------------------
 | ID1| SNAME |
-+----+-------+----------
 | 1  | NAME1 | 
-+----+-------+----------
 | 2  | NAME2 | 
-+----+-------+----------
 | 3  | NAME3 | 
------------------------

TB1
-------------
 | ID1  |   |**___                    TB2
-------------     \___              -----------------------
 | SNAME|   |         \___          |  ID2  |         |   
-------------             |___      +-------+---------+---
                              \==>  | IDTB1 |         |
                                    +-------+---------+---  
                                    |  SID2 |         |
                                    +-------+---------+---
                                    |SVALUE |         |
                                    +-------+---------+-- 

TB2
ID2 IDTB1   SID2   SVALUE

----------------------------------
 |ID2 |IDTB1| SID2 | SVALUE  |
-+----+-----+---------------------
 | 1  |  1  |   5  |   11    |
-+----+-----+--------------------
 | 2  |  1  |   7  |    9    |
-|----|-----+--------------------
 | 3  |  1  |   7  |   10    |
-|----|-----+--------------------
 | 4  |  1  |   7  |   13    |
-|----|-----+--------------------
 | 5  |  2  |   5  |   14    |
-|----|-----+--------------------
 | 6  |  2  |   5  |   16    |
-|----|-----+--------------------
 | 7  |  2  |   5  |   18    |
-|----|-----+--------------------
 | 8  |  2  |   7  |   10    |
-|----|-----+--------------------
 | 9  |  3  |   5  |   10    |
-|----|-----+--------------------
 | 10 |  3  |   1  |   10    |
-|----|-----+--------------------
 | 11 |  3  |   7  |   22    |
---------------------------------



I want query to show results like:
example: where SID2=7

----------------------------
 | ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
 |  4  | NAME1 |   13   |
-+-----+-------+--------+---
 |  8  | NAME2 |   10   |
-+-----+-------+--------+---
 |  11 | NAME3 |   22   |
----------------------------


What I have tried:

SELECT TB1.SNAME,  TB2.ID2,  ( select max(SVALUE) from tb2 where TB2.SID2 = 7 ) as SVALUE FROM dbo.TB1 INNER JOIN dbo.TB2   ON TB1.ID = TB2.IDTB1 WHERE TB2.SID2 = 7
Posted
Updated 18-Mar-17 6:38am
Comments
[no name] 18-Mar-17 10:05am    
SELECT
TB2.ID2,
TB1.SNAME,
TB2.SVALUE
LEFT JOIN TB2 ON TB2.IDTTB1 = TB1.ID1
WHERE TB2.SID2 = '7'


should do it
spotligh_ly 18-Mar-17 16:42pm    
Thanks brother /
but I want the return one value IDTB1
When the maximum value SValue or ID2
which easy
When apply your query these results:
----------------------------
| ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
| 2 | NAME1 | 9 |
-+-----+-------+--------+---
| 3 | NAME1 | 10 |
-+-----+-------+--------+---
| 4 | NAME1 | 13 |
-+-----+-------+--------+---
| 8 | NAME2 | 10 |
-+-----+-------+--------+---
| 11 | NAME3 | 22 |
-+-----+-------+--------+---


but i need results like
----------------------------
| ID2 | SNAME | SVALUE |
-+-----+-------+--------+---
| 4 | NAME1 | 13 |
-+-----+-------+--------+---
| 8 | NAME2 | 10 |
-+-----+-------+--------+---
| 11 | NAME3 | 22 |
----------------------------

1 solution

This should do it and I think your sample output is wrong. The max for IDTB1 is 18 and not 10

First select all the max using ROW_NUMBER over function from TB2 then join Tb1
SQL
SELECT  Id2, Sname, SValue
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
        FROM    dbo.TB2
        ) q
JOIN dbo.TB1
ON q.[IDTB1] = dbo.TB1.[Id1]
WHERE   rn = 1

Output:
Id2	Sname	SValue
4	Name1	13
7	Name2	18
11	Name3	22
 
Share this answer
 
Comments
spotligh_ly 18-Mar-17 17:06pm    
Thanks for help ur query it very near what i need ,return max value of (SVALUE)
but you forget there another condition SID2 =value
return max (SVALUE) where SID2 =7 exmpl.
Bryian Tan 18-Mar-17 17:10pm    
Try add that filter right after dbo.tbl2
spotligh_ly 18-Mar-17 17:14pm    
SELECT
Id2, Sname, SValue
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
FROM dbo.TB2
) q
JOIN dbo.TB1
ON q.IDTB1 = TB1.ID
WHERE rn = 1 and q.SID2=7
but the result
--------------------------------------
Id2 Sname SValue
4 NAME1 13
11 NAME3 22
Bryian Tan 18-Mar-17 17:21pm    
Did you try place the filter like this... from dbo.tb2 where sid2= 7
spotligh_ly 18-Mar-17 17:21pm    
thanks problem is solved
SELECT
Id2, Sname, SValue
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY [IDTB1] ORDER BY svalue DESC) AS rn
FROM dbo.TB2 where SID2=7
) q
JOIN dbo.TB1
ON q.IDTB1 = TB1.ID
WHERE rn = 1

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900