Consulting

Results 1 to 4 of 4

Thread: How to union two tables without losing ordering

  1. #1

    How to union two tables without losing ordering

    Hello I use this sql sheme
    TABLE (... ORDER BY ...)
    UNION
    TABLE (... ORDER BY ...)
    I have this two table
    1-14.6.2017 - - -
    1491219,95 1519 981,71 1
    4540487,32 4273 1062,6 2
    7793302,9 6484 1201,93 3
    2007632,09 2313 867,98 4
    3370946,87 2600 1296,52 5
    3458361,78 2735 1264,48 6
    and
    15-31.6.2017 - - -
    1912881,67 1819 1051,61 1
    4874438,49 4572 1066,15 2
    8925168,88 7427 1201,72 3
    2318450,72 2736 847,39 4
    3759952,25 2949 1274,99 5
    1766691,22 1376 1283,93 6

    but i get
    1-14.6.2017 - - -
    1491219,95 1519 981,71 1
    15-31.6.2017 - - -
    1766691,22 1376 1283,93 6
    1912881,67 1819 1051,61 1
    2007632,09 2313 867,98 4
    2318450,72 2736 847,39 4
    3370946,87 2600 1296,52 5
    3458361,78 2735 1264,48 6
    3759952,25 2949 1274,99 5
    4540487,32 4273 1062,6 2
    4874438,49 4572 1066,15 2
    7793302,9 6484 1201,93 3
    8925168,88 7427 1201,72 3
    How to just join two tables without losing ordering?
    Attached Files Attached Files

  2. #2
    with UNION ALL i get better result but stiil lose subordering.
    1-14.6.2017 - - -
    1491219,95 1519 981,71 1
    2007632,09 2313 867,98 4
    3370946,87 2600 1296,52 5
    3458361,78 2735 1264,48 6
    4540487,32 4273 1062,6 2
    7793302,9 6484 1201,93 3
    15-31.6.2017 - - -
    1766691,22 1376 1283,93 6
    1912881,67 1819 1051,61 1
    2318450,72 2736 847,39 4
    3759952,25 2949 1274,99 5
    4874438,49 4572 1066,15 2
    8925168,88 7427 1201,72 3

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    Without the tables it is difficult to test anything, but I can suggest that you try places the first table order after the union, something like
    TABLE (SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 1 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 1 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 2 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 2 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 3 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 3 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 4 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 4 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 5 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 5 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 6 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 1 And Day([ДатаОперации]) <= 14 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 6 ))) AS test UNION SELECT '1-14.6.2017' AS СуммаЧека,'-' AS КолвоЧеков,'-' AS СредЗнач, '-' AS НомерСтанции FROM Rosneft)
    UNION
    TABLE (SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 1 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 1 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 2 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 2 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 3 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 3 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 4 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 4 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 5 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 5 ))) AS test UNION SELECT Round(Sum(test.MaxOfрубливкассувесьчек),2) AS СуммаЧека, COUNT(*) AS КолвоЧеков,Round(Sum(test.MaxOfрубливкассувесьчек)/COUNT(*),2) AS СредЗнач, 6 AS НомерСтанции FROM (SELECT Rosneft.TRANS_ID, Max(Rosneft.рубливкассувесьчек) AS MaxOfрубливкассувесьчек FROM Rosneft WHERE Month([ДатаОперации]) = 6 And Day([ДатаОперации]) >= 15 And Day([ДатаОперации]) <= 31 GROUP BY Rosneft.TRANS_ID, Rosneft.[Станция(номер)] HAVING (((Rosneft.[Станция(номер)])= 6 ))) AS test UNION SELECT '15-31.6.2017' AS СуммаЧека,'-' AS КолвоЧеков,'-' AS СредЗнач, '-' AS НомерСтанции FROM Rosneft
    ORDER BY table1.НомерСтанции, table2.НомерСтанции)

    where table1 and table2 are the names of the tables.

    I am an Access man, not an SQL man, so SQL is not my forte.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    ps you might have more answers if you ask the mods to move this and your other post to the SQL forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •