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
ADVERTISEMENT
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
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
Apr 10, 2008
Hello everyone,
I have a little problem here. I need to select data from multiple columns and multiple tables, some of those columns can have NULL value. I have tried few things, and havent find the right way so far. Problem that i am incountering is that when i run SELECT statment as it is right now, i dont get back results which match search parameters but have NULL value in one or more column in select statment. How can i fix this problem?
SELECT Person2role.person2roleID,Person.firstname, Person.lastname, Person.sex
FROM Person LEFT OUTER JOIN
Person2Role ON Person.personID = Person2Role.personID
WHERE (Person.firstname LIKE '%' + ISNULL(@firstname + '%', ''))
AND (Person.lastname LIKE '%' + ISNULL(@lastname + '%', ''))
AND (Person.sex = ISNULL(@sex, Person.sex))
This is a part of SELECT statment..just so you can get the idea of what i am doing..i have few more parameters
I am working with SQL Server 2005 Express Version
Thank you,
Alex
View 10 Replies
View Related
Oct 31, 2014
‘Trying to SELECT INTO a new table all columns of a table based on a DISTINCT value of one column so for example:
SELECT *
INTO new_table
FROM old_name
WHERE old_table.column IS DISTINCT’
View 4 Replies
View Related
May 6, 2015
Using SSRS 2008 r2...I have a report with a single-value parameter and three multi-value parameters, Class1, Name2 and Name3. I'm hoping for an explanation to one thing that I'm seeing and information on a second thing.
Class1 and Name2 both have the (Select All) parameter selected but Class1 is displaying the concatenated parameter variable list whereas Name2 is showing Null. Why is that? If anything, how can I get Class1 to be similar to Name2 and show Null?But my desired wish is to have Class1, Name2 and Name3 display the text"All Selected" when the parameter (Select All) is chosen.
View 3 Replies
View Related
Dec 10, 2007
I get the rows(records) I want by removing the RequestDateTime column from the Select query SELECT distinct et.configname as 'Part Number', substring(email,(charindex('@',email)+1),len(email)) as 'email ending',u.company_name as 'Company',u.city as 'City',u.country as 'Country' --,RequestDateTime as 'Date Downloaded'from new_products.dbo.modelrequests etleft outer join cc_host.dbo.usr_list u on et.username = convert(varchar(50),u.[user_id])where interfacename like '%download%'and result=0and u.country = 'JP'and CONVERT(DATETIME, RequestDateTime, 102)>= convert(DATETIME,'12/5/2007', 102)GROUP BY email,et.configname,u.company_name,u.city,u.country,requestdatetime I want to show the RequestDateTime for the 1600 records that do get selected. How can I put RequestDateTime back in the resulting table output without it being part of the Distinct clause?
View 3 Replies
View Related
Mar 3, 2008
Hello,
When I am using DISTINCT in the SELECT clause for a table having a column of datatype "ntext" in SQL 2000 as well SQL 2005, it fails giving the error as "ntext data type cannot be selected as DISTINCT". This is coz
"The text, ntext, or image data type cannot be selected as DISTINCT."
But I am able to insert into a temporary table having a column whose datatype is nvarchar) using this SELECT clause in SQL 2000. Please advice me how this happens. If I am not worng, implicit conversion has happened. But this insert fails raising the same error in SQL 2005. Please help me to find the difference
thanks in advance!
View 3 Replies
View Related
May 28, 2008
I'm having a problem building a query for an application I'm writing. Basically, there are a bunch of records in a table, and some of them may have the same identifier code. I only want one of each identifier code. The table looks like this:
Memo
---------
Msg_Identifier varchar(60)
Memo_ID int (pk)
Subject varchar(64)
Message text
Priority int
Deleted int
My query:
SELECT DISTINCT Msg_Identifier, Memo_ID, Subject, Message, Priority FROM Memo WHERE Deleted = '0'
When I run this query, I get "The text data type cannot be selected as DISTINCT because it is not comparable."
I only want Msg_Identifier to be the distinct field, and it's obviously not set up as text. I've used SELECT DISTINCT in the past but I don't ever recall hitting an issue like this. What's the best way to do it?
View 8 Replies
View Related
Jul 31, 2007
Hi all,
i have a table which has date and count as column
Can you please...help me write a query for it.
date | type | count
10/07 | A | 70
11/07 | A | 50
12/07 | A | 50
11/07 | B | 60
12/07 | B | 50
13/07 | B | 40
11/07 | C | 50
12/07 | C | 60
13/07 | C |60
14/07 | A | 50
i need to write a sp such that..if user sends date and @ndays(no. of days) as parameter say 11/07 and 3
then
out put
type 11/07 12/07 13/07
A 50 50
B 60 50 40
C 50 60 60
Thanks..
Regards,
sradez
View 3 Replies
View Related
Nov 19, 2007
Hello,
I start with this table,
Tagname
Timestamp
Value
A
12/12/2007 12:00:01
1
A
12/12/2007 12:00:02
1,1
A
12/12/2007 12:00:03
30
B
12/12/2007 12:00:01
12
B
12/12/2007 12:00:02
12
B
12/12/2007 12:00:03
12
C
12/12/2007 12:00:01
13,4
C
12/12/2007 12:00:02
14
C
12/12/2007 12:00:03
14,5
I would like the table to be as following
Timestamp
A
B
C
12/12/2007 12:00:01
1
12
13,4
12/12/2007 12:00:02
1,1
12
14
12/12/2007 12:00:03
30
12
14,5
Does anyone knows how to do this within a query?
Thx,
Nic
View 9 Replies
View Related
Mar 18, 2015
I have created row level security on two views and adding these two views to particular role.Today I have got an requirement that, middle level managers shouldn't see the all the columns. So I have created another role for Middle level managers and assign securables as those two views with selected columns by grant, and map all the middle level managers to this role. I thought my job is done. But these managers uses this view on SSAS(tabular model) and Excel, In those applications they are not able to load the data.
Later I come to know we can't use -- select * from ViewA ( in viewA I have restristced few columns in the role level) Work around is creating another view and assigning to the role. But how can we achieve column level security to implement this in either SSAS/SSRS/EXCEL?
View 6 Replies
View Related
Apr 9, 2014
I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
View 1 Replies
View Related
Apr 28, 2015
I am getting error [[Msg 16924, Level 16, State 1, Line 13
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.]] when i execute below script.
Declare @mSql1 Nvarchar(MAX)
declare @dropuser int
declare @dbname Nvarchar(max)
declare @username Nvarchar(max)
DECLARE Dropuser_Cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Apr 4, 2007
Hi,I have an Excel file with 400 rows of old values and the correspondingnew values. My table currently has 10 columns out of which 3 columnsuse the old value specified in the excel file. I need to update thoseold values in the columns with the new values from the Excel file.Please guide me as to how to proceed with this.Thanks in advance!
View 4 Replies
View Related
Jun 12, 2007
i have the table name is "Books". its contains 10 Rows.... I want select 5,6 rows,or 7,8 not first and last..... Any tell me the Query for that...
Regards,
S.sajan
View 3 Replies
View Related
Mar 29, 2007
My parameter pass the id of the parameter to my query while its name is displyed on the parameter combo.
I have a report on which there is a text field.
1,On this field i want's to display the name of the parameter.
If select all check box is check i want's to show Report of AllFieldName.
other wise it should be comma separate like Report of aa,cc.
e.g
I have field myfield with fieldname aa,bb,cc dd value with fieldid as 1,2,3,4
I am passing this parameter in the query like
select ... where id in( @fieldid)
2,I am exporting this report in a excel format.
How can i pass the parameter in the query string ,submit it.
{How to check select all parameter or particular parameter e.t.c }
View 4 Replies
View Related
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
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
Jun 7, 2004
Hello,
i want to Select all articles form a category without an article that is selected.
Thanx a lot !
View 1 Replies
View Related
Jan 22, 2008
I am working in .net 2.0.
How can I check if "(Select All)" option was selected in multi-value parameter?
Thanks in advance!
View 4 Replies
View Related
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
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
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
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
Feb 13, 2007
Hi All
I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.
Many Thanks
Delli
View 4 Replies
View Related
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
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
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
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
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
View Related
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
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
