I have a table with customers and their referenses, I need populate anaother table with the first references in the group and then the secound query to populate the 2nd reference.
So I have records like this:
2 4205 CHRIS ROGERS
3 4205 AMANDA HOWARD
4 2256 Jim Welch
5 2256 Bill Horthone
So I need a query that will first retun all the names that come first in the customer ID
Then I need to be able to change the query so that I get all the 2nd names in the cust omer ID.
So the the first one would return:
ID CustID FirstNmae LastNmae
2 4205 CHRIS ROGERS
4 2256 Jim Welch
The 2nd query returns this :
3 4205 AMANDA HOWARD
5 2256 Bill Horthone
I have trryed different varyations of something like this, but it just returns both records in the group.
SELECT Min([R_Index]), [Last Name]) AS "Last name"
FROM [PLM].[dbo].[References]
Where [Customer ID] = '2157'
Group by [R_Index]
Order by [R_Index]
I think something like this might work for you. I would start with GROUP and MIN/MAX for the first query and then self join it back to itself.
IF OBJECT_ID('tempdb..#TEMPTable') IS NOT NULL
BEGIN
DROP TABLE #TEMPTable
END
-----------------------------------------------------------------
CREATE TABLE #TEMPTable
(
-- [MyID] [uniqueidentifier] ROWGUIDCOL NOT NULL
[My_ID] INT NOT NULL
, [Customer_ID] INT NOT NULL
, [First_Name] VARCHAR(50) NOT NULL
, [Last_Name] VARCHAR(50) NOT NULL
) ON [PRIMARY]
INSERT INTO #TEMPTable
(
[My_ID]
, [Customer_ID]
, [First_Name]
, [Last_Name]
) --VALUEs
SELECT 2, 4205, 'CHRIS', 'ROGERS'
UNION ALL
SELECT 3, 4205, 'AMANDA', 'HOWARD'
UNION ALL
SELECT 4, 2256, 'JIM', 'WELCH'
UNION ALL
SELECT 5, 2256, 'BILL', 'HORTHONE'
--SELECT * FROM #TEMPTable
-----------------------------------------------------------
SELECT T1.* FROM #TEMPTable T1
INNER JOIN
(
SELECT [Customer_ID], MIN([My_ID]) AS My_ID FROM #TEMPTable
GROUP BY [Customer_ID]
) G1 ON G1.My_ID = T1.My_ID
ORDER BY T1.My_ID
-----------------------------------------------------------
SELECT T1.* FROM #TEMPTable T1
INNER JOIN
(
SELECT [Customer_ID], MAX([My_ID]) AS My_ID FROM #TEMPTable
GROUP BY [Customer_ID]
) G1 ON G1.My_ID = T1.My_ID
ORDER BY T1.My_ID
-----------------------------------------------------------
USE tempdb
go
DROP TABLE #temptable
go
use tempdb
go
drop table #TEMPTable
go
CREATE TABLE #TEMPTable
(
-- [MyID] [uniqueidentifier] ROWGUIDCOL NOT NULL
[My_ID] INT NOT NULL
, [Customer_ID] INT NOT NULL
, [First_Name] VARCHAR(50) NOT NULL
, [Last_Name] VARCHAR(50) NOT NULL
) ON [PRIMARY]
INSERT INTO #TEMPTable
(
[My_ID]
, [Customer_ID]
, [First_Name]
, [Last_Name]
) --VALUEs
SELECT 2, 4205, 'CHRIS', 'ROGERS'
UNION ALL
SELECT 3, 4205, 'AMANDA', 'HOWARD'
UNION ALL
SELECT 4, 2256, 'JIM', 'WELCH'
UNION ALL
SELECT 5, 2256, 'BILL', 'HORTHONE'
select * from #TEMPTable
go
SQL....
SELECT Row_number()
OVER(
partition BY customer_id
ORDER BY my_id),
*
FROM #temptable
ORDER BY Row_number()
OVER(
partition BY customer_id
ORDER BY my_id),
my_id
go
