Zapytanie LEFT JOIN do tej samej tabeli mssql aby uzyskać losową wartość z innej kolumny bez grupowania

Question

Witam, z góry przepraszam za tytuł, nie miałem pomysłu jak to sensownie opisać.

A więc tak mam tabelę w której przechowywane są informacje o operacjach przeprowadzonych na produktach.

Wykonuje zapytanie które ma mi zliczyć aktualny stan produktów:

SELECT 
	[a].[Item], 
	SUM([a].[Quantity]) AS [Count]
FROM 
	[products] [a]
WHERE 
	[a].[Location] IN ([...])
GROUP BY 
	[a].[Item]
ORDER BY [Count] DESC, [a].[Item] DESC

Potrzebuję do tego jeszcze wyciągnąć informację z kolumny [Serial], problem w tym że każdy z nich będzie inny a ja potrzebuję znać zapas całego zestawu zgrupowanego po [Item]. Z racji, że [Serial] może być dowolny to spróbowałem wykonać takie zapytanie:

SELECT 
	[a].[Item], 
	SUM([a].[Quantity]) AS [Count],
	[b].[Serial]
FROM 
	[products] [a]
	LEFT JOIN (SELECT TOP(1)
					[a].[Item],
					[a].[Serial]
				FROM 
					[products] [a]
				WHERE 
					[a].[Serial] != '') [b] ON [b].[Item] = [a].[Item]	
WHERE 
	[a].[Location] IN ([...])
GROUP BY 
	[a].[Item], [b].[Serial]
ORDER BY [Count] DESC, [a].[Item] DESC

Jednak kolumna [Serial] dla wszystkich pozycji zwraca NULL

I tak w sumie nie bardzo rozumiem co jest źle w tej składni. 

Zapytania są tutaj uproszczone aby zobrazować problem

Comments

Potrzebuję do tego jeszcze wyciągnąć informację z kolumny [Serial], problem w tym że każdy z nich będzie inny

Chyba nie rozumiem. Pokaż jakąś przykładową zawartość tabelek i co ma wyjść. Nie rozumiem czym ma być ten serial.

 

---

Item -> Numer porządkowy 'LAPTOP/0001'

Quantity -> Stan operacji +1 / -1

Serial => Numer seryjny danego urządzenia

+------------+----------+--------------+
|    Item    | Quantity |    Serial    |
+------------+----------+--------------+
| LAPTOP/001 | 1        | 11111        |
+------------+----------+--------------+
| LAPTOP/001 | 1        | 11112        |
+------------+----------+--------------+
| LAPTOP/001 | 1        | 11113        |
+------------+----------+--------------+
| LAPTOP/001 | 1        | 11114        |
+------------+----------+--------------+
| LAPTOP/001 | -1       | 11111        |
+------------+----------+--------------+
| LAPTOP/002 | 1        | 22200        |
+------------+----------+--------------+
| LAPTOP/002 | 1        | 22201        |
+------------+----------+--------------+
| LAPTOP/002 | 1        | 22203        |
+------------+----------+--------------+
| LAPTOP/002 | -1       | 22200        |
+------------+----------+--------------+
| LAPTOP/002 | 1        | 22204        |
+------------+----------+--------------+
| LAPTOP/002 | -1       | 22201        |
+------------+----------+--------------+

Finalnie daje nam to 
4 Szt. LAPTOP/001
2 Szt. LAPTOP/002

W tej tabeli zapisywane są operacje na produktach, dodanie odjęcie, przesunięcie itd.
Kluczem tablicy jest kolumna Item LAPTOP/001 / LAPTOP/002 itd
Ja wyświetlam dane w postaci zsumowanej zawartości kolumny Quantity, zgrupowanej po Item,
I potrzebuję losowy numer Serial do działania innej części programu 

Answer 1

Oh, czyli to nie jest lista produktów, tylko log operacji. Bardzo myląca ta nazwa, sugeruję zmienić.

(btw ten `[products] [a]` to samo-sabotaż ;) Ten kod był generowany czy sam go tak napisałeś?)

W każdym razie kilka propozycji:

SELECT
    product.Item,
    sum(product.Quantity) AS Count,
    string_agg(product.Serial, ' ') AS Serials
FROM
    product
WHERE
    product.Location IN (...)
GROUP BY
    product.Item
ORDER BY Count DESC, product.Item DESC

Da Ci obok każdego itema stringową listę wszystkich seriali w danej grupie, albo np

    max(product.Serial) AS Serials

Da po prostu największy.

Comments

Tak właśnie podejrzewałem jak drugi raz czytałem co napisałem że ktoś mógł to odebrać jako listę magazynową a nie log operacji,

max(Serial) To właśnie to czego szukałem! Dzięki.

A co do [products] [a] piszę to tak z dwóch powodów:

1. Zapytanie jest dużo, dużo większe, dochodzi do litery L

2. Pracuje na zew. oprogramowaniu i mam z góry narzucone nazwy tabel, a są one... Przykłady:

[Item Ledger Entry$437dbf0e-84ff-417a-965d-ed2bb9650972]

[Item Attribute Value Mapping$437dbf0e-84ff-417a-965d-ed2bb9650972]

[Item Attribute Value$437dbf0e-84ff-417a-965d-ed2bb9650972]

Więc skracanie sobie tego do kolejnych liter alfabetu znacznie skraca mi kod i ułatwia połapanie się w tym