quote:

---

Originally posted by madhivanan

Which is the unique key?
Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by balajiie

Just U need a Row That Recently Posted Right

its simple Try This

Select Top 1 * from table_name order by DATEENT desc

All is well
Balaji

---

quote:

---

Originally posted by balajiie

Just U need a Row That Recently Posted Right

its simple Try This

Select Top 1 * from table_name order by DATEENT desc

All is well
Balaji

---

quote:

---

Originally posted by balajiie

hi senthil sorry for the interference .. i am a beginner i am not able to understand your query..please explain it

All is well
Balaji

---

quote:

---

Originally posted by balajiie

Thank U so Much Madhi...only one doubt ..shall v use distinct keyword for this operation

All is well
Balaji

---

quote:

---

Originally posted by ms65g

SELECT t1.custid, t2.comment 
FROM (SELECT DISTINCT custid
        FROM your_table) t1(c)
CROSS APPLY (SELECT TOP 1 comment
               FROM your_table t
              WHERE t.c = t1.custid
              ORDER BY DATEENT DESC) t2;

---

SELECT *
  FROM table_name T1
 WHERE NOT EXISTS
       (SELECT *
          FROM table_name T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
         
SELECT *
  FROM table_name T1
 WHERE DATEENT >= ALL (SELECT DATEENT
                         FROM table_name T2
                        WHERE T1.custid = T2.custid);

quote:

---

Originally posted by ms65g

--> How did you forget your own advice? <--
Would you like so standard form?

SELECT *
  FROM table_name T1
 WHERE NOT EXISTS
       (SELECT *
          FROM table_name T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
         
SELECT *
  FROM table_name T1
 WHERE DATEENT >= ALL (SELECT DATEENT
                         FROM table_name T2
                        WHERE T1.custid = T2.custid);

---

quote:

---

Originally posted by madhivanan

quote:

---

Originally posted by ms65g

--> How did you forget your own advice? <--
Would you like so standard form?

SELECT *
  FROM table_name T1
 WHERE NOT EXISTS
       (SELECT *
          FROM table_name T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
         
SELECT *
  FROM table_name T1
 WHERE DATEENT >= ALL (SELECT DATEENT
                         FROM table_name T2
                        WHERE T1.custid = T2.custid);

---

Can you post performance results based on various methods?

Madhivanan

Failing to plan is Planning to fail

---

SET NOCOUNT ON;
DECLARE @t TABLE (CUSTID CHAR(4), DATEENT DECIMAL(9,0), COMMENT VARCHAR(50));
INSERT @t VALUES
('WALL',20020805,'Some comments here'),
('SRAN',20030805,'Booking slect optiojdhjjhjh'),
('SRAN',20030915,'Postponedjjhjhjjhjhj'),
('SRAN',20100211,'Delivery add changed'),
('RAMM',20090205,'New cystome paid'),
('TRAK',20100122,'Based outside of the ciy');

SET STATISTICS IO ON
SET STATISTICS TIME ON

--Your Query
select custid,comment from @t as t
where DATEENT=(select max(DATEENT) from @t where custid=t.custid)
/*

 Batch Cost: 47%
Table '#76818E95'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 35 ms.
*/--My Query             
SELECT custid, comment
  FROM @t T1
 WHERE NOT EXISTS
       (SELECT *
          FROM @t T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
/*
 Batch Cost: 21%

Table '#70C8B53F'. Scan count 2, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 28 ms.
*/
SET STATISTICS IO OFF
SET STATISTICS TIME ON

SET NOCOUNT ON;
DECLARE @t TABLE (CUSTID CHAR(4), DATEENT DECIMAL(9,0), COMMENT VARCHAR(50));

INSERT @t VALUES
('WALL',20020805,'Some comments here'),
('SRAN',20030805,'Booking slect optiojdhjjhjh'),
('SRAN',20030915,'Postponedjjhjhjjhjhj'),
('SRAN',20100211,'Delivery add changed'),
('RAMM',20090205,'New cystome paid'),
('TRAK',20100122,'Based outside of the ciy');
INSERT @t 
SELECT t1.* from @t as t1 cross join @t as t2 cross join @t as t3 cross join @t as t4 cross join @t as t5 cross join @t as t6


SET STATISTICS IO ON
SET STATISTICS TIME ON

--Your Query
select custid,comment from @t as t
where DATEENT=(select max(DATEENT) from @t where custid=t.custid)

SELECT custid, comment
  FROM @t T1
 WHERE NOT EXISTS
       (SELECT *
          FROM @t T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
SET STATISTICS IO OFF
SET STATISTICS TIME ON

SET NOCOUNT ON;
DECLARE @t TABLE (CUSTID CHAR(4), DATEENT DECIMAL(9,0), COMMENT VARCHAR(50));

INSERT @t VALUES
('WALL',20020805,'Some comments here'),
('SRAN',20030805,'Booking slect optiojdhjjhjh'),
('SRAN',20030915,'Postponedjjhjhjjhjhj'),
('SRAN',20100211,'Delivery add changed'),
('RAMM',20090205,'New cystome paid'),
('TRAK',20100122,'Based outside of the ciy');
INSERT @t 
SELECT t1.* from @t as t1 cross join @t as t2 cross join @t as t3 cross join @t as t4 cross join @t as t5 cross join @t as t6


SET STATISTICS IO ON
SET STATISTICS TIME ON

select custid,comment from @t as t
where DATEENT=(select max(DATEENT) from @t where custid=t.custid)
/*
Table '#290D0E62'. Scan count 1, logical reads 248, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 141 ms,  elapsed time = 291 ms.
   */
SELECT custid, comment
  FROM @t T1
 WHERE NOT EXISTS
       (SELECT *
          FROM @t T2
         WHERE T1.DATEENT < T2.DATEENT
           AND T2.custid = T1.custid);
/*
Table '#290D0E62'. Scan count 2, logical reads 7730586, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 385281 ms,  elapsed time = 389001 ms.
   */
SET STATISTICS IO OFF
SET STATISTICS TIME ON