Max isn't working in my query
-
Hello,
I have this script which is an extract of my database. I don't design it, so the structure will not change.
I have two tables with ids and ids_done like repairs in progress and done.
CREATE TABLE ids
(
requestID varchar(50),
orderID varchar(50)
);
CREATE TABLE ids_done
(
requestID varchar(50),
orderID varchar(50)
);
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200013','ABC1706200013@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200013-1','ABC1706200013-1@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014','ABC1706200014@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-1','ABC1706200014-1@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-2','ABC1706200014-2@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-3','ABC1706200014-3@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-4','ABC1706200014-4@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-5','ABC1706200014-5@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-6','ABC1706200014-6@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-7','ABC1706200014-7@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-8','ABC1706200014-8@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-9','ABC1706200014-9@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-A','ABC1706200014-A@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-B','ABC1706200014-B@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200014-C','ABC1706200014-C@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200014-D','ABC1706200014-D@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200015','ABC1706200015@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200016','ABC1706200016@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200016-1','ABC1706200016-1@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200016-2','ABC1706200016-2@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200016-3','ABC1706200016-3@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200017','ABC1706200017@@1' );
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200017-2','ABC1706200017-2@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200017-1','ABC1706200017-1@@1' );
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200017-3','ABC1706200017-3@@1' );
SELECT
MAX(SUBSTRING(ol.orderID, LEN(ol.orderID) - 3, 1)) as Maxi,
COALESCE(ol.requestID,cl.requestID ) as requestID
,COALESCE(ol.orderID, cl.orderID) as orderID
FROM ids ol
LEFT join ids_done cl ON cl.orderID LIKE SUBSTRING(ol.orderID, 1, 13 ) + '%'
GROUP BY COALESCE(ol.requestID,cl.requestID )
,COALESCE(ol.orderID, cl.orderID)
ORDER BY COALESCE(ol.orderID, cl.orderID)I want to have max order_id for both tables like this :
The increment have these rules :
The first repair is without any '-'.
If the client cancel it, a repair with -1 is created and the first one is transfer to the done table
And so one for next.
The size is limited to 15 letters, that why there is the alphabet after.Is it possible to help me and obtain the max for all repairs ?
Regards
-
I think it's the GROUP BY with a COALESCE that's mixing it up, maybe if you figure out the base of the requestID for grouping?
SELECT MAX(requestID), MAX(orderID)
FROM
(SELECT CASE WHEN CHARINDEX('-', requestID) = 0 THEN requestID ELSE LEFT(requestID, LEN(requestID) - 2) END AS baserequestID, requestID, orderID
FROM ids
UNION ALL
SELECT CASE WHEN CHARINDEX('-', requestID) = 0 THEN requestID ELSE LEFT(requestID, LEN(requestID) - 2) END AS baserequestID, requestID, orderID
FROM ids_done) u
GROUP BY baserequestID -
Hello,
The result of your query is good.
But as I want the last -x with the '@@1' part, I set the select like this :
SELECT MAX(requestID), CONCAT(MAX(orderID),'@@1')But if you add these lines :
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200018’,’ABC1706200018@@1’);
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200018’,’ABC1706200018@@2’);
The @@2 line will not appear at the end.
So how can I have it ?
-
I'm confused...the query Chris provided does work--it brings back the '...@@2' value after inserting the 2 '%18%' values.
????
What are you expecting from the query that you are not currently getting?
Alan H
MCSE - Data Management and Analytics
Senior SQL Server DBA
Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/ -
I ran those INSERT statements as you have them in my sandbox database, and reran my query, and I get:
requestID orderID
ABC1706200013-1 ABC1706200013@@1ABC1706200013@@1
ABC1706200014-D ABC1706200014@@1ABC1706200014@@1
ABC1706200015 ABC1706200015@@1ABC1706200015@@1
ABC1706200016-3 ABC1706200016@@1ABC1706200016@@1
ABC1706200017-3 ABC1706200017@@1ABC1706200017@@1
ABC1706200018 ABC1706200018@@2ABC1706200018@@2 -
Hello,
I want something like :
There are last ids, last -x ids and last '@@x' ids.
The @@2 is when someone change the order a little bit, the system create and the '@@2' and close the '@@1'.To add a sample with a -x :
INSERT INTO ids(requestID, orderID) VALUES ('ABC1706200018-1’,’ABC1706200018-1@@2’);
INSERT INTO ids_done(requestID, orderID) VALUES ('ABC1706200018-1’,’ABC1706200018-1@@1’);
The result should be :
I'm not the owner of the database and can't change the way the tool operate and use it.
So I use the database and can't do any changes.
Regards -
ah, sorry, I see my mistake, the @ comes after -
SELECT requestID, orderID
FROM
(SELECT baserequestID, requestID, orderID, ROW_NUMBER() OVER (PARTITION BY baserequestID ORDER BY requestID DESC, orderID DESC) AS recentness
FROM
(SELECT CASE WHEN CHARINDEX('-', requestID) = 0 THEN requestID ELSE LEFT(requestID, LEN(requestID) - 2) END AS baserequestID, requestID, orderID
FROM ids
UNION ALL
SELECT CASE WHEN CHARINDEX('-', requestID) = 0 THEN requestID ELSE LEFT(requestID, LEN(requestID) - 2) END AS baserequestID, requestID, orderID
FROM ids_done) u
) rn
WHERE rn.recentness = 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply