Sql-server – how to get unique records

sql serversql-server-2008

Table structure

CREATE TABLE [dbo].[Order Details2](
    [OrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL   DEFAULT (0),
    [Quantity] [smallint] NOT NULL   DEFAULT (1),
    [Discount] [real] NOT NULL   DEFAULT (0),
 CONSTRAINT [PK_Order_Details2] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC,
    [ProductID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Table values

+-------------+-----------+------------+----------+----------+
|     OrderID | ProductID | UnitePrice | Quantity | Discount |
+-------------+-----------+------------+----------+----------+
|       10248 |        11 | 14         |       12 | 0        |
|       10248 |        42 | 9.8        |       10 | 0        |
|       10248 |        72 | 34.8       |        5 | 0        |
|       10249 |        14 | 18.6       |        9 | 0        |
|       10249 |        51 | 42.4       |       40 | 0        |
|       10250 |        41 | 7.7        |       10 | 0        |
|       10250 |        51 | 42.4       |       35 | 0.15     |
|       10250 |        65 | 16.8       |       15 | 0.15     |
|       10251 |        22 | 16.8       |        6 | 0.05     |
|       10251 |        57 | 15.6       |       15 | 0.05     |
|       10251 |        65 | 16.8       |       20 | 0        |
|             |           |            |          |          |
+-------------+-----------+------------+----------+----------+

Required output

+-------------+-----------+------------+----------+----------+
|     OrderID | ProductID | UnitePrice | Quantity | Discount |
+-------------+-----------+------------+----------+----------+
|       10248 |        72 | 14         |       12 |        0 |
|       10249 |        51 | 18.6       |        9 |        0 |
|       10250 |        65 | 7.7        |       10 |        0 |
+-------------+-----------+------------+----------+----------+

My table has two primary keys, I want to get the unique record from this table. The required output contain column orderid and max productid row (just one row) only. I need helping hand to solve this issue.

If have any query please ask.

Best Answer

Just JOIN on a subquery:

SELECT *
FROM MyTable T
INNER JOIN (SELECT OrderID, MAX(ProductID) as ProductID
            FROM MyTable
            GROUP BY OrderID) x
  ON x.orderid = t.orderid
  AND x.productid = t.productid

Related Solutions

Sql-server – How to get back some deleted records

Is your database in full recovery mode?

If yes, are you doing transaction log backups?
- If yes, do you have a backup utility like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLSafe?
  - If yes, those utilities can undelete objects from backup files (including the fulls and transaction logs) - but explaining how to use those is beyond the scope of what I can do here. Contact the vendor for instructions.
  - If no, restore the full backup and transaction logs as a database with a different name. (Don't overwrite the existing database.) You'll be able to get an up-to-the-second copy of the object before the delete happened, but you'll need to use the stopat part of the restore command to specify when to stop restoring the commands. You need to stop before the delete happened.
- If no, go grab a copy of a log reader utility like Quest LiteSpeed or Apex SQL Log. These utilities can connect to the database server, examine the log file, and help you undo transactions. I'm not sure if the demo versions will work, but the paid ones definitely will.
If no (not in full recovery mode), restore the last full backup as a database with a different name. (Don't overwrite the database you've already got.) From there, you'll be able to restore whatever records were online at the time of the backup, but you'll lose all of the changes since.

Sql-server – Get most recent 100 records from the database

Based on the comments above, what you will need is something like this:

create table #topdates (ID int not null, TableName nvarchar(128), systimestamp smalldatetime)
exec sp_MSforeachtable @command1 = 'insert into #topdates select top 100 ID, ''?'' as TableName, systimestamp from ? order by systimestamp desc'
select top 100 * from #topdates
order by systimestamp
drop table #topdates

This will collate the top 100 results from each table into a temporary table, and then return the top 100 rows to you.

EDIT: Based on further information provided in the comments, it was established that the ID field might actually have a different name on every table. The following SQL will list the top 100 last edited records in the entire database, across all tables, showing the Primary key value, the table name, and the systimestamp:

create table #keys (TABLE_NAME nvarchar(128), COLUMN_NAME nvarchar(128))
insert into #keys
SELECT cu.TABLE_NAME, cu.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu 
WHERE EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
    WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
        AND tc.CONSTRAINT_NAME = cu.CONSTRAINT_NAME)
create table #topdates (Primary_Key nvarchar(128), TableName nvarchar(128), systimestamp smalldatetime)
declare @table nvarchar(128), @Column nvarchar(128), @sql nvarchar(max)
DECLARE cur CURSOR FOR select * from #keys 
OPEN cur
FETCH NEXT FROM cur INTO @table, @Column 
WHILE @@FETCH_STATUS = 0
BEGIN
    select @sql = 'insert into #topdates select top 100 [' + @Column + '], ''' + @table + ''', systimestamp from [' + @table + '] order by 3 desc '
    exec sp_executesql @sql 
    FETCH NEXT FROM cur INTO @table, @Column
 END
CLOSE cur
DEALLOCATE cur
select top 100 * from #topdates order by systimestamp desc, TableName, Primary_Key
drop table #keys 
drop table #topdates

You can easily extend this to show the column name of the Primary Key column too.

Best Answer

Related Solutions

Sql-server – How to get back some deleted records

Sql-server – Get most recent 100 records from the database

Related Question