| Author |
 Topic  |
|
|
homelessnick
Starting Member
3 Posts |
 Posted - 02/21/2013 : 06:21:02
|
Hi everyone!
I have a food menu table with the following structure:
MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
611 1 NP0030
611 2 NS0862
611 3 NG4376
Which means that menu number 320 has 3 plates, with its respective order and FoodId. MenuId 611 is exactly the same, only changes the menuID
The problem is that I need to create another table containing unique menus, for example:
MenuId Order FoodId
------ ----- ------
001 1 NP0030
001 2 NS0862
001 3 NG4376
Any advice or help would be appreciated. Thanks in advance!
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 02/21/2013 : 07:05:01
|
DECLARE @tab TABLE(MenuId INT, [Order] INT, FoodId VARCHAR(10))
INSERT INTO @tab
SELECT 320, 1, 'NP0030' UNION ALL
SELECT 320, 2, 'NS0862' UNION ALL
SELECT 320, 3, 'NG4376' UNION ALL
SELECT 611, 1, 'NP0030' UNION ALL
SELECT 611, 2, 'NS0862' UNION ALL
SELECT 611, 3, 'NG4376'
SELECT RIGHT( '000'+ CAST(DENSE_RANK() OVER( Order BY (SELECT 1)) AS VARCHAR(5)), 3) MenuId, [Order], FoodId
FROM @tab
GROUP BY [Order], FoodId
--
Chandu |
 |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
Posted - 02/21/2013 : 07:22:47
|
create table #yak (MenuID varchar(3), [Order] int, FoodID varchar(6))
insert into #yak (MenuID, [Order], FoodID)
values
('320',1,'NP0030')
,('320',2,'NS0862')
,('320',3,'NG4376')
,('611',1,'NP0030')
,('611',2,'NS0862')
,('611',3,'NG4376')
,('225',1,'NM0908')
,('225',2,'NJ0889')
select * from #yak
select
MenuID
,ROW_NUMBER()OVER(ORDER BY MenuID) as [Order]
,FoodID
from
(select distinct '001' as MenuID
,foodid
from #yak) y
MenuID Order FoodID
001 1 NG4376
001 2 NJ0889
001 3 NM0908
001 4 NP0030
001 5 NS0862
I added a few extra records to see if this is what the OP meant. My way is MUCH more clumsy  , but i do not get a repeating order number.
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
|
|
|
|
homelessnick
Starting Member
3 Posts |
Posted - 02/21/2013 : 09:18:40
|
Thanks in advance for the quick replies, but I think I have explained myself correctly.
What I need is to know how many unique menus I have in my table, not creating one menu made of all the plates.
For example:
MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
611 1 NP0030
611 2 NS0862
611 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1232
Would return:
320 1 NP0030
320 2 NS0862
320 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1232
MenuId 611 disapears because its duplicated (MenuId 320 goes first and its the same menu)
Thanks again!
|
|
|
|
homelessnick
Starting Member
3 Posts |
Posted - 02/21/2013 : 09:29:33
|
At least I need to know if a menu in table 1 exists in table 2. I mean
Table 1
MenuId Order FoodId
------ ----- ------
999 1 NP0030
999 2 NS0862
999 3 NG4376
Table 2
MenuId Order FoodId
------ ----- ------
320 1 NP0030
320 2 NS0862
320 3 NG4376
555 1 DS0974
555 2 DS8374
555 3 DS1234
MenuId 999 exists in table 2, with the MenuId 320
Thanks again!!! |
|
|
| |
Topic |
|
|
|
Multiple records comparation
