How can i select a max of column witch type is unique identifier

_{Saturday, July 23, 2016 5:47 PM}

Hi,

I have a table with a structure as bellow

create table shpro(
    Gid uniqueidentifier,
    id int,
    prod varchar(30),
    orderdate datetime,
    shipdate datetime
    )

Get the max of the orderdate and shipdate and also removing the duplicate data i tried the query as bellow

select
        MAX(p1.Gid) Gid,
        MAX(p1.id) id,

       MAX(p1.prod) prod,

        MAX(p1.orderdate) orderdate,
        MAX(p2.shipdate) shipdate
 from shpro p1 inner join shpro p2
on p1.id=p2.id
where cast(p1.orderdate as DATE)>GETDATE() and cast(p1.shipdate as DATE)<GETDATE()-1
group by p1.prod

I have got the error Operand data type uniqueidentifier is invalid for max operator.

what i did is trying to convert the column with unique identifier to varchar i tried the query as bellow

select
        MAX(cast(p1.Gid as varchar(36))) Gid,
        MAX(p1.id) id,

       MAX(p1.prod) prod,

        MAX(p1.orderdate) orderdate,
        MAX(p2.shipdate) shipdate
 from shpro p1 inner join shpro p2
on p1.id=p2.id
where cast(p1.orderdate as DATE)>GETDATE() and cast(p1.shipdate as DATE)<GETDATE()-1
group by p1.prod

it give me a result but wrong result

I have an idea using CTE but i want something without using CTE for performance

Someone can help me ?

_{Sunday, July 24, 2016 2:19 AM ✅Answered | 1 vote}

Could you tell us what it is you are trying to do (like the business problem you are trying to solve).  The query you have will return one row for each prod.  It will have the largest Gid, id, prod, orderdate and shipdate for that prod in any row that meets your ON and WHERE conditions.  Note that it is entirely possible that you would get the Gid from one row, the id from another, the orderdate from a third and the shipdate from a fourth row.

It is hard for me to imagine that that is what you actually want.  But maybe it is.  I guessing you really want something like "the values in the row which meets my ON and Where condition and has the largest orderdate for each product and, if two rows for a prod have the same orderdate, then you want the one with the largest shipdate.  Then you would want something like what RajivBala gave you, but just adding your join and WHERE condition.  So something like

;With cte As
(SELECT  p1.Gid,p1.id ,p1.prod,p1.orderdate,p2.shipdate ,
  row_number() over(partition by p1.prod order by p1.orderdate desc, p2.shipdate desc) as rn
  from shpro p1 inner join shpro p2
on p1.id=p2.id
where cast(p1.orderdate as DATE)>GETDATE() and cast(p1.shipdate as DATE)<GETDATE()-1)
select Gid,id prod,orderdate,shipdate
from cte;

Tom

_{Saturday, July 23, 2016 6:53 PM}

What do you mean by "it gives me a result, but the wrong result"?  What do you define as the correct result?  The result you get will be the largest value sorted alphabetically after you convert the uniqueidentifier to a 36 character string.  The value of a uniqueidentifier really has no meaning except as an identifier for a row that is effectively unique across all systems.  If you create one uniqueidentifier and later create a second one, the first one might be greater than the second or it might be less than the second.

Tom

_{Saturday, July 23, 2016 7:27 PM}

 
 -- if your requirement is just to GET the max of the orderdate and shipdate - for Every "Prod" 
-- also removing the duplicate data 
-- not using CTE , avoid guid Casting 
-- Get Latest Row : for each "Prod" Based on Latest orderdate and shipdate 


SELECT  Gid,id ,prod,orderdate,shipdate FROM 
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY prod  ORDER BY orderDate DESC, ShipDate DESC) AS rn FROM Shpro
) AS sh_pro
WHERE rn =1



/*

Here i have NOT included the Condition ( Future(After Today) OrderDates && Past (Before Yesterday ShippinDates) )
where cast(p1.orderdate as DATE)>GETDATE() and cast(p1.shipdate as DATE)<GETDATE()-1

-- Pls let me know if any Change needed here 
*/

_{Saturday, July 23, 2016 8:07 PM}

As Tom says, it is not clear what is the right result here, but cast to binary(16) and see if that gives you a result more to your liking.

_{Saturday, July 23, 2016 8:55 PM}

exactly what i mean as bellow

I have a table who returns me a value as bellow

    155488EA-FF70-49D7-99AB-AFD4125F3435  dell    14296188    2016-06-21 05:51    2016-06-21 06:19    **
**    1B640883-0DB6-4255-B1ED-770B6578064C  dell    14295943    2016-06-21 04:37    2016-06-21 04:39

I want the `max` of the value i have tried a sql but i have the result as bellow :

    1B640883-0DB6-4255-B1ED-770B6578064C  dell    14296188    2016-06-21 05:51    2016-06-21 06:19

But the result that i need is this one :

    155488EA-FF70-49D7-99AB-AFD4125F3435  dell    14296188    2016-06-21 05:51    2016-06-21 06:19

_{Saturday, July 23, 2016 8:55 PM}

What i mean i don't have the right result  please  see as bellow

exactly what i mean as bellow

I have a table who returns me a value as bellow

    155488EA-FF70-49D7-99AB-AFD4125F3435  dell    14296188    2016-06-21 05:51    2016-06-21 06:19    **
**    1B640883-0DB6-4255-B1ED-770B6578064C  dell    14295943    2016-06-21 04:37    2016-06-21 04:39

I want the `max` of the value i have tried a sql but i have the result as bellow :

    1B640883-0DB6-4255-B1ED-770B6578064C  dell    14296188    2016-06-21 05:51    2016-06-21 06:19

But the result that i need is this one :

    155488EA-FF70-49D7-99AB-AFD4125F3435  dell    14296188    2016-06-21 05:51    2016-06-21 06:19

_{Saturday, July 23, 2016 8:57 PM}

thanks for reply but can i please include

the Condition ( Future(After Today) OrderDates && Past (Before Yesterday ShippinDates) )
where cast(p1.orderdate as DATE)>GETDATE() and cast(p1.shipdate as DATE)<GETDATE()-1

_{Saturday, July 23, 2016 9:54 PM}

>> I have a table with a structure as below <<

No, this is not a table at all. A table has a key, but your "deck of punch cards" cannot ever have a key, because all the columns can be null. Why do you think a good can ever be used inside a schema as part of an attribute in a table? It is used only for referencing things outside the schema, and never inside it. There is no such thing as a global magical "id" in RDBMS; it has to be the identifier of something in particular. Then identifiers are never numerics because you do not do any math on them. In short, you have no idea how to build a valid data model. Let us try and fix that part first;

CREATE TABLE Shipments
(shipment_id CHAR(15) NOT NULL PRIMARY KEY,
 product_gtin CHAR(15) NOT NULL,   Google GTIN
 order_date DATE NOT NULL,
 ship_date DATE NOT NULL 
 CHECK (order_date <= ship_date)
 );

>> Get the max of the order_date and ship_date and also removing the duplicate data <<

Why would you have duplicate data in a valid table? That is why tables have keys! Why did you use datetime and set of date for a data type? You are just wasting resources doing the cast and converts. Also the old getdate () from Sybase has been replaced by the ANSI/ISO standard current_timestamp for a few years now.

>> I have got the error Operand data type uniqueidentifier is invalid for max operator.<<

Yes, of course. Think about it. It references something outside the schema sitting out there on the web or hyperspace or wherever! It is not a quantity! Just because it looks like a number does not mean that it is.

>> what I did is trying to convert the column with unique identifier to varchar I tried the query as below <<

WHY?? What would that string mean in any kind of data model? This is as silly as asking "On a scale from 1 to 10, what color is your favorite letter of the alphabet?"

This will get you the shipments for the most recent shipping date:

WITH X
AS 
(SELECT shipment_id, product_gtin, order_date, ship_date,
         MAX(ship_date) OVER () AS last_shipment_date
   FROM Shipments

 WHERE chip_date <= CAST (CURRENT_TIMESTAMP AS DATE))

SELECT X.shipment_id, X.product_gtin, X.order_date, X,ship_date
  FROM X
 WHERE X.ship_date = x.last_ship_date;

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

_{Saturday, July 23, 2016 10:16 PM}

So did you try casting to binary (and casting back to uniqueidentifier after the MAX)?

You don't say why you want over the other, but I assume that you are thinking of how the bits are ordered. (For some reason in the textual representation of a GUID, the bytes have been rearranged a bit.)

_{Saturday, July 23, 2016 10:24 PM}

yes i tried this one i have the same problem