November 25, 2015 at 3:46 pm
Folks, I usually had one row in table containing multiple values with nulls, which is relatively easy when comes to selectin existing value only but was wondering what will be the best approach to select one of the existing rows in reference table without writing some magic function, which I seen in post here
I have two tables ,one
CREATE TABLE [dbo].[ProductTable](
[ID] [int] ,
[ProductName] [varchar](50) ,
[ProductDesc] [varchar](50) ,
)
with two products in it
1,phone,coms
2,mobile,coms
and example lkp table
CREATE TABLE [dbo].[lkpTable](
[ID] [int] ,
[Flag] [int] ,
[value] [varchar](50) ,
)
go
with following data
id, flag, value
1,2400,A
1,1400,B
2,1400,B
where id is the same in both tables ProductTable.id=lkpTable.id
each product in table will always have default temp B value with 1400 flag but may not have new allocated A value
I was trying to select max(flag) value by matching ID but not getting any luck with it.
idea is that if there is no value A with flag 2400 allocated , I should be selecting B value as default or say next available value for this ID
so result should be something like this
id, flag, value
1,Phone,A
2,Mobile,B
any help appreciated , not sure how to bite this one .
November 25, 2015 at 4:30 pm
got a brain wave , maybe there is better way of doing this but this looks like its working for me
SELECT b.value, a.productname
FROM b RIGHT OUTER JOIN
a ON b.id = a.id
where b.flag=(select max(b.flag) from b
RIGHT OUTER JOIN
a ON b.id = a.id)
GROUP BY b.value, a.productname
November 27, 2015 at 2:08 pm
This may be more efficient (most people use LEFT rather than RIGHT join, so I switched it to that):
SELECT l.value, p.productname
FROM dbo.ProductTable p
LEFT OUTER JOIN (
SELECT ID, value, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY flag DESC) AS row_num
FROM dbo.lkpTable
) AS l ON l.ID = p.ID AND l.row_num = 1
GROUP BY l.value, p.productname
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 2, 2015 at 2:31 pm
ScottPletcher (11/27/2015)
This may be more efficient (most people use LEFT rather than RIGHT join, so I switched it to that):
SELECT l.value, p.productname
FROM dbo.ProductTable p
LEFT OUTER JOIN (
SELECT ID, value, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY flag DESC) AS row_num
FROM dbo.lkpTable
) AS l ON l.ID = p.ID AND l.row_num = 1
GROUP BY l.value, p.productname
thank you for suggestion .
December 2, 2015 at 4:43 pm
Select SQL
SELECT p.*
, xLkp.*
FROM dbo.ProductTable p
CROSS APPLY -- We know table dbo.lkpTable will always have atleast one (default) row for each product in [dbo].[ProductTable]
(
SELECT TOP (1) *
FROM dbo.lkpTable l
WHERE l.ID = p.ID
ORDER BY l.Flag DESC -- Sort descending and then pick top 1
) xLkp ;
Create Test
CREATE TABLE [dbo].[ProductTable]
(
[ID] [int]
, [ProductName] [varchar](50)
, [ProductDesc] [varchar](50)
) ;
INSERT INTO [dbo].[ProductTable]
SELECT 1, 'phone', 'coms' UNION ALL
SELECT 2, 'mobile', 'coms' ;
CREATE TABLE [dbo].[lkpTable]
(
[ID] [int]
, [Flag] [int]
, [value] [varchar](50)
) ;
INSERT INTO [dbo].[lkpTable]
SELECT 1, 2400, 'A' UNION ALL
SELECT 1, 1400, 'B' UNION ALL
SELECT 2, 1400, 'B' ;
SELECT * FROM dbo.ProductTable ;
SELECT * FROM dbo.lkpTable ;
December 4, 2015 at 3:19 am
As well as only one value is needed scalar subquery will do
SELECT p.*,
value = ( SELECT TOP (1) [value]
FROM dbo.lkpTable l
WHERE l.ID = p.ID
ORDER BY l.Flag DESC
)
FROM dbo.ProductTable p
;
December 7, 2015 at 2:22 am
serg-52 (12/4/2015)
As well as only one value is needed scalar subquery will do
SELECT p.*,
value = ( SELECT TOP (1) [value]
FROM dbo.lkpTable l
WHERE l.ID = p.ID
ORDER BY l.Flag DESC
)
FROM dbo.ProductTable p
;
Thx Serg-52 that's handy too
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply