How To Display All Columns Of Distinct Rows

Apr 17, 2014

I have the below working query

select distinct OrderNum from invoiceHistory where orderNum in (56387,57930,57933,57935)

I need to get all columns of the distinct ordernum as resultset. I am working to get the resultset of below.

select invoiceID,distinct orderNum,invoiceDate from invoiceHistory where orderNum in (56387,57930,57933,57935)

View 1 Replies


ADVERTISEMENT

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?

Jul 6, 2007

Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache



When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."



Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View 2 Replies View Related

Count Of Distinct Rows Of Only Selected Columns?

Aug 9, 2012

I am trying to just get a count of the number of distinct rows are in a table --- not looking at the entire row of fields, but only selecting a few.

i don't want to see the distinct rows, i just want a count of how many are in the table.

View 4 Replies View Related

How To Show Distinct Rows Of The Column Of The Dataset And Number Of Distinct Rows Of That Column

Mar 29, 2007

suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"

I want's some thing like

field1 rowcount
aaa 5
bbb 6

View 1 Replies View Related

Reporting Services :: Display Columns When There Is No Data To Display

Apr 30, 2015

I would like to display a portion of report where there is data or no data

There is data subreport  display   

     Product Name Latex Gloves  
     Product ID      
xxxx5678

 There NO data in the subReport
 
  Product Name                          
   Product ID    

View 3 Replies View Related

Display Distinct Records?

Dec 7, 2011

TableName: Order_Archive
Fields:
orderid
load_date
filename
order_date
dollar

I load a file each week into a table, each file has unique orderid, load_date, filename, order_date and dollar. However the same orderid, order_date and dollar could appear in another file with different load_date and file_name.

File1:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-01', 'File1', '2011-01-01', '102'
'1002', '2011-01-01', 'File1', '2011-01-01', '103'

File2:
orderid, load_date, file_name, order_date, dollar
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

Question:
whats is the best way to retrieve the distinct records that has the most recent load_date? expected results below:

Expected Results:
orderid, load_date, file_name, order_date, dollar
'1000', '2011-01-01', 'File1', '2011-01-01', '101'
'1001', '2011-01-08', 'File2', '2011-01-01', '102'
'1002', '2011-01-08', 'File2', '2011-01-01', '103'
'1003', '2011-01-08', 'File2', '2011-01-01', '104'

View 3 Replies View Related

Reporting - Display Distinct Value

Jan 22, 2007

How do I display only those rows with single occurance value in the LID column?

Original table:
SID | LID
94 | 2
222 | 2
130 | 3
82 | 4
99 | 5
161 | 5
103 | 6
161 | 7
99 | 7
103 | 8

Desired output:

SID | LID
130 | 3
82 | 4
103 | 6
103 | 8

Thanks in advance!

View 2 Replies View Related

Select Distinct Rows From Duplicate Rows....

Nov 28, 2007

Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!

View 4 Replies View Related

Distinct At Two Columns

Mar 8, 2007

I have this stored procedure:
ALTER PROCEDURE usp_My_Procedure  ( @Country varchar(5) )  AS SELECT DISTINCT City, Short FROM Table1 WHERE Country = @Country
RETURN
I want to select just one of each 'city' and 'short' in the database....But this is not working correct......Whats wrong?
Lets say that I have a table that looks something like this
City               Short
New York       NY
Los Angeles   LA
Lake Alice      LA
Los Angeles   LosAng

View 1 Replies View Related

How To Get The Distinct Of All Columns

Feb 17, 2014

i have written a query to get the latest modified objects in Database and my problem is it is giving because of case condition.

how to get the distinct of all Columns .

Name
Raj
NULL
Raju
NULL
NULL
Ram

required output :

Name
Raj
Raju
Ram

here is my query

SELECT CASE WHEN type = 'TT' THEN name ELSE '' END AS TableType,
CASE WHEN type = 'P' THEN name ELSE '' END AS Procedures,
CASE WHEN type = 'FN' THEN name ELSE '' END AS Functions,
CASE WHEN type = 'U' THEN name ELSE '' END AS Tables,
CASE WHEN type = 'V' THEN name ELSE '' END AS Views,
CASE WHEN type = 'TR' THEN name ELSE '' END AS Triggers
FROM sys.objects
WHERE type IN ('TT','FN','U','V','P','TR')
AND DATEDIFF(D,modify_date,GETDATE())< 5

View 4 Replies View Related

Getting The Right Distinct Rows

Jan 21, 2008

I have a database for a CMS I have made, which has a column called ‘tag’ everytime a page is updated it inserts another row in to the table with the same tag but with a updated date. i use this method so i have a version history
What I want to get out is rows that have distinct tag columns and is also the newest row associated with that ‘tag’.

View 3 Replies View Related

Insert Distinct Columns?

Apr 17, 2007

I have two rows of data:
Customer as string, Date as int
 These values are predefined so I cannot use autoindex.  I have to make sure that when I insert a row of data that it does not exist already int the database.  there can be duplicate customers and duplicate dates but not duplicate customer/date combinations.  What would the sql query be?
if    (select count(1) from customers where customer = @customer and date = @date) =< 0  
    insert into table1(customer, date) values (@customer, @date)
end if

View 2 Replies View Related

How To Get Distinct Columns Using COALESCE

Aug 20, 2007

Hi guys, can you please help me to solve this problem.  I have to get distinct row from offering column of xyz table.
 I have to get offering1, offering2 from xyz table. But I am getting only offering1. I should not get duplicate rows from XYZ table.
 SELECT DISTINCT @Staging_Off= COALESCE(@Staging_Off + ',', '')+ Offering
FROM xyz
WHERE xyz.Offering NOT IN( SELECT DISTINCT Offering.Offering
FROM Offering Join xyzON Offering.Offering= xyz.Offering
AND Offering.SourceSystem= @SourceSystem
)

View 1 Replies View Related

Max With Distinct Two Columns And Corresponding Third Field

Apr 30, 2014

i need to write a query and can't get it to work no matter how it try. Here's what i need:

T1
-------
a1
a2
datetime
a4
a5
.....

i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me.for every specific a1 there is many entries of a2 + timedate etc.

Code:
T1
-----------------------------
a1 a2 timedate

1 1 2014-04-31 10:59:38.000 ......
1 2 2014-04-31 10:59:39.000 .......
1 3 2014-04-31 10:59:39.500 .......
2 1 timedate .......
2 2 timedate .......etc

select distinct t1.a1
,max (t1.a2)
from t1

View 5 Replies View Related

Distinct But Include All The Columns

Nov 1, 2013

I have this but how can I get all the columns of the row? I only want them distinct per these 2 cols.

SELECT DISTINCT OrdHst.ordernbr, OrdNbr.trans
FROM OrdHst JOIN OrdNbr
ON OrdHst.ordernbr = OrdNbr.ordernbr
ORDER BY 1,2

Could return:

12345 001
12345 AAA
12345 BBB
12345 CCC
12345 PWB

View 2 Replies View Related

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)

Dec 25, 2005

Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1   answer1   answer2   answer3user2   answer1   answer2   answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View 1 Replies View Related

Selecting Distinct Rows

Jun 11, 2008

Hi,
I want to select the 8 most saled products from large orders table... the problem is that when i use the "distinct" sentence (something like this- "SELECT TOP 8 distinct id, products, productid FROM tbl_orders ORDER BY id") I get back the distinct of any columns.... (and any ID is distinct, of course), but if i don't include the id's in the distinct sentence, i can't order by id's.
 can i get the last orders, only by distinct product, and not by distinct id, and order them by the id's?
  

View 17 Replies View Related

Select Distinct Rows

Mar 19, 2004

Hi,

I'm having a little bit of trouble trying to figure out how to do this query, right now I have:

SELECT I.AppItemId, P.ProductID, P.PartNum, P.Relist, I.AppUserId
FROM ProductsToRelist I join Products P on P.ProductID = I.AppSKU
WHERE P.Relist = 1 and I.AppStatus = 5 and Not I.AppItemId is Null

and it returns something like this:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
1665 -30987 SELECT_OOS11
2433 -30987 SELECT_OOS11
4272 -30984 SELECT11
2436 -30984 SELECT11
2793 -32708 SELECT11


But I only it want it to return 1 record for each ProductID like so:

AppItemId ProductID PartNum Relist AppUserId
2786 -32730 SELECT_OOS11
2787 -32729 SELECT12
2788 -32727 SELECT_OOS11
4269 -30987 SELECT_OOS12
4272 -30984 SELECT11
2793 -32708 SELECT11


ProductID is the primary key for the Products table, and a product can be in the ProductsToRelist table many times but each row would have a unique AppItemId. I know that I need to use Distinct or a different kind of join, but I'm not sure which. How would you suggest to do this?

Thanks

View 4 Replies View Related

Select Distinct For Different Rows

Jun 5, 2006

I have the following tablecolumns:  [col1], [col2],[col3] and [NAME].I want to select the name column for each row where [col1]='07'.The problem is that there are several rows where [col1] contains '07' and also the name is the same. [col2] and [col3] contain different data for these double rows...however, I cant use the [col1] and [col2] values in my query because I dont know what values they contain beforehand.So now, when I execute my query and add the DISTINCT key I still get all the double rows!I hope this explains my problem, help is really appreciated...ow, btw: deleting the double rows is not an option....

View 4 Replies View Related

Selecting Distinct Rows ??

Oct 27, 1998

View 3 Replies View Related

DISTINCT Only Certain Columns In On Table And Pull Their IDs

Mar 9, 2007

Please help. (I am using ASP, VB, SQL)I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

View 5 Replies View Related

SELECT DISTINCT - But Just One Of The Selected Columns

Sep 22, 2005

Hi,

I have a table with 3 columns - userID, score, dateCompleted

From this table I wish to select the highest score for each userID and the date for that score. If there is more than one row with the same userID and score (i.e. the user got the same score twice), I want to select the earliest date.

To clarify, there are multiple rows which have the same userID, and may have the same score too, but I want to pull out just 1 score for each userID.

I could do this quite easily if the DISTINCT operator worked on a per column basis, but it works on the entire row instead. Is there a way to SELECT DISTINCT column, but still output other columns in those rows?

I hope this is clear, but please let me know if I've confused you.

View 3 Replies View Related

Forcing Specific Distinct Columns....

Dec 6, 2006

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:


SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC


The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
83630Mathew Smith
8 366Tony Wattage

Which is kinda annoying.... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--Philkills

View 14 Replies View Related

T-SQL (SS2K8) :: Max With Distinct Two Columns And Corresponding Third Column

Apr 30, 2014

T1
-------
a1
a2
datetime
a4
a5
.....

i need distinct max between a1&a2 which i can get no problem but i cant get that unique datetime that correspond to a1&a2 in 1 query because this is will a subquery in a big query. Creating another temp table etc is not an option for me. for every specific a1 there is many entries of a2 + timedate etc.

create table abc_test (
id int
,runs int
,date1 datetime

[code]....

I can either get distinct ID + latest date or ID + largest #ofRuns, both will do but also need the third column.

View 5 Replies View Related

Compact / Distinct Multiple Columns

Jun 13, 2008

Hi,

I have a table with the following example data

idcol1col2col3
----------------------------
1abcnullnull
1abcnullnull
1null123null
4alphanullnull
4alphanullgamma
1abc987null


I would want to reduce that somehow to
idcol1col2col3
-----------------------------
1abc123null
1abc987null
4alphabetagamma

First thing in my mind was distinct over multiple columns, but I haven't found any resource mentioning that possibility.
I don't immediatly see a way without (complex) looping.
Can anybody put me on the right track?

btw, I know that Id is not an id.
It's a table var that holds an id of a table combined with fields of other tables.

View 20 Replies View Related

Selecting Distinct On 3 Columns In Ms Sql Server

Feb 26, 2006

Hi, I think you can do this in oracle but I'm trying to figure a way to do it in ms sql.. I need to select a distinct combination of columns, like so...

select distinct(ItemName,ItemData,FID) from tblSIFups

Does anyone know how to achieve that? I have multiple data where I shouldn't and I don't have any control over the application so I need to clean it this way.

thanks, nicki

View 1 Replies View Related

Slecting Multiple Distinct Columns..

Mar 7, 2006

Hi all...I have a table in which some columns has distinct values and some hasduplicates..i wan to select all the columns with distinct values....noproblem if rows has null value in it....i tried a lot wit distinct andgroup by but nothing got worked out...Waitin for your reply.....Thanking you...

View 2 Replies View Related

Count Multiple Distinct Columns

Jul 20, 2005

I want to build query to return how many rows are in this query:select distinct c1, c2 from t1But SQL won't accept this syntax:select count (distinct c1, c2) from t1Does someone know how to count multiple distinct columns? Thanks.--Disclaimer: This post is solely an individual opinion and does not speak onbehalf of any organization.

View 3 Replies View Related

T-SQL (SS2K8) :: Rows Into Columns - Remove Duplicates And Variable Rows

Aug 5, 2014

I managed to transpose rows into columns.

;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference

[code]...

But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..

ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view

ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW

View 6 Replies View Related

Turn Columns Into Rows And Rows Into Columns

Jan 24, 2008

I have a report which is a list of items and I display everything about the item. It is great. My report table in the layout tab is simple. Header,Detail,Footer. Each Item has 65 columns. The number of items (rows) vary upon what you want to see. Example data.
Item#, Description, CaseSalePrice, Cost, BottleSalePrice, Discount
123, Grenadine, 100.00, 75.00, 15.50, 2.00
456, Lime Juice, 120.00, 81.00, 17.25, 2.00

There could be 1 item or 4000 items.

What I want to see is.

Item # - 123, 456
Description - Grenadine, Lime Juice
CaseSalePrice - 100.00, 120.00
Cost - 75.00, 81.00
BottleSalePrice - 15.50, 17.25
Discount - 2.00, 2.00

What I am actually doing is running this the top example and saving to excel. Then copying the sheet. Creating a new sheet then doing a paste special transpose and this gives the users what they want to see.

I want to grab that table object in the report layout tab and twist it 90degrees so the header is on the left, detail is in the middle and the footer is on the right. It would be perfect.

The dynamic column need is really the problem here. I never know how many items will be in the report. They all have the same basic information like description and pricing.

I am all out of creative ideas, any help would be appreciated.

View 6 Replies View Related

Return Rows That Arent Distinct

May 5, 2008

I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber.

How do i return disticnt records having count greater than 1

View 5 Replies View Related

Distinct Random Rows Using NewID()

May 26, 2008

I have 2 tables, Artists and Artworks.
I have a query:

SELECT TOP (4) dbo.Artists.ArtistID, dbo.Artists.FirstName + ' ' + dbo.Artists.LastName AS FullName, dbo.Artworks.ArtworkName, dbo.Artworks.Image
FROM dbo.Artists INNER JOIN
dbo.Artworks ON dbo.Artists.ArtistID = dbo.Artworks.ArtistID
ORDER BY NEWID()

This query returns random images, but the artists are sometimes repeated.
I would like to have DISTINCT Random Artists returned, each with a random image. I tried various subqueries, but I just get error messages.
Any help would be appreciated.
Thnks,

Paolo

View 8 Replies View Related

SQL Counting Number Of Non-distinct Rows?

May 3, 2006

Hi, I have a table that for ease has this data in:R1, R2, R....z---------------------A | 12A | 22A | 30B | 0B | -1B | -3C | 100I want to generate a table for each distinct row in R1, gives a countof all the rows with data correspondingFor the above table I would getA | 3B | 3C | 1Im probably being stupid but cannot see this at the moment... pleasehelp.Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved