How To Get The Last Occurence Of Rows Containing Disticnt Value In One Column
Jul 23, 2005
I have a table that tracks GPS records broadly speaking as follows
tsDATETIME
usernameVARCHAR(16)
...
GPS data
...
I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.
For a single user I know I can do
SELECT TOP 1 * from <table> order by ts desc
But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.
I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/
View 4 Replies
ADVERTISEMENT
Jun 25, 2015
I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.
View 28 Replies
View Related
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
Dec 2, 2003
Hi all,
Is there a function in SQL that will return the number of occurence of 1 string to the other.
e.g.
string1 = "1"
string2 = "2131145"
function should return 3
How do i obtain this in SQL?
Thanks in advance
View 9 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 2 Replies
View Related
Mar 29, 2001
When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
View 1 Replies
View Related
Jan 6, 2004
Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record.
It would be a great help to me.
Thanks,
John
View 4 Replies
View Related
Mar 1, 2006
Hi all,How can I get a row that has only one occurence in a table? Notthrough 'distinct' because this gets a single row that might havemultiple occurences, I want to get only rows that have only oneoccurence in the table.I tried the group by and within the having clause I placed thecount(myfield) = 1, did not work...select f1, f2, count(f2)from table1inner join table2 on table1.id = table2.fidwhere .....group by f1, f2having count(f2) = 1gives me result like this: (basically in this situation, I dont want toreturn any results)f1 f2 count(f1)--- --- -----------1 a 12 a 1any ideas sql gurus?thanks
View 3 Replies
View Related
Aug 27, 2006
Hi ...I have a weblog database where I want to count the occurences of atable of string values that appear in all the urls viewed.My tblWebLog as a field that contains the url ...tblWebLog.[cs-uri-stem]I have another table ... tblStrings ... that has a field [strSearch]for a string value and an integer field [intViewCount] to count theoccurence of the string in tblWebLog.[cs-uri-stem]I've been trying ...Update tblStringsSet [intViewCount] = (Select Count(*) From tblWebLog Where[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%').... but it doesn't fly and I'm stumped. Any thoughts?Cheers.
View 2 Replies
View Related
Aug 7, 2007
We have a strange occurrence in one of our dev Sql server. Our platform is Sql Server 2000 on SP4. Build Number is 8.00.2039
TableA
Sid int, --Non-Clustered Index
Aid int,
Uid int,
€¦..
TableB
Iid int,
Sid int, ---No foreign key constraint to TableA.Sid but logically refers to that column. --Non-Clustered Index
€¦..
Now, we are working on schema changes, now TableB looks like
TableB
Iid int,
Uid int, ---Logically refers to TableA.Uid
€¦..
Sid in TableB is dropped and a new column Uid is added.
And there is no Sid in TableB.
We have an existing SP that refers to TableB.Sid like
Create proc sp_ab
As
Select Uid from TableA where Sid = (Select Sid from TableB where Iid = @Iid)
After making the schema changes listed above, if I try to compile this SP without making any changes to the procedure, it should fail. Surprisingly it passes compilation and also retrieves data using hash joins and doing table scans on TableA avoiding index seeks. This SP used to take less than second, now takes almost 90 seconds to retrieve data. I couldn't replicate this problem with another set of objects.
If I make changes to the SP sp_ab like
Create proc sp_ab
As
Select Uid from TableA where Uid = (Select Uid from TableB where Iid = @Iid), it runs less than a second as it should.
Whats surprising is, if I do 'Select Sid from TableB where Iid = 1 ' in Query analyzer, it throws error referring to invalid column Sid in TableB but couldn't throw this error when the sql is wrapped in a SP. Everything else in the DB and in the db server seems to be ok except this.
If I query Syscolumns table, I couldn't see any references to Sid in TableB after the schema chnages and not sure why the code in SP is not caught during compilation. Any clues.
View 1 Replies
View Related
Mar 17, 2008
Hi All,
I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph.
Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working?
Thanks a lot in advance and let me know if the question is not clear.
Manoj Deshpande.
View 4 Replies
View Related
Jun 15, 2001
Hi Friends:
I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.
For example, I run the following queries in oracle to get the desire result.
SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;
INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2
SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;
INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4
SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;
INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6
SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;
INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10
Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?
--Raj
View 4 Replies
View Related
Jan 10, 2008
Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL
and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic
i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons
example
getseachIndex("M|d|d","|",2)==> the result is 4
View 3 Replies
View Related
May 1, 2015
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. Â To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.Â
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1Â
View 2 Replies
View Related
Feb 27, 2008
Hello,
I need to concatenate a column from multiple rows into a single column in a new table.
How can I do this?
SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR
Need to have
SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR
THANK YOU
View 10 Replies
View Related
Mar 7, 2008
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
Morten
View 9 Replies
View Related
Sep 26, 2000
hi
i have a query that returns 1 column 5 rows which i want as 1 row 5 columns
SELECT columna
FROM atable
would become??
thanx
Liju
View 5 Replies
View Related
Jan 11, 2006
How to put two rows of data in one column?
from
dealID dealNO
1 888
1 999
to
dealID dealNO
1 888,999
you can't use create permission, so can't create storedprocedure.
Pls help.
View 4 Replies
View Related
Oct 31, 2007
Hello,can I do this via SQL:example: tbltest has 5 rows:col1===ACFMRWhat I want is this:result:===============A, C, F, M, RDo I really have to go through the rows per SP? I could do this:SELECT UDF(col1)FROM tbltestAin't there a more simple way, maybe theres a T-SQL-command ?thxcandide_sh
View 6 Replies
View Related
Dec 11, 2007
I want to convert the row value as column name
example:-->
value ratio
3 4.166666666661 1.315789473680 00 0
To :->
value ratio ratio1 ratio2 ratio3
3 4.166 1.315 0 0
any ideas?
View 1 Replies
View Related
Apr 5, 2001
I have a table that has a column for each month and I want to use a view to convert each row into 12 rows with a one month column. The month column will have 1 - 12 in it depending on the month. I need to convert it this way to push it into an Essbase cube. I know I can use the union operator to do this, but I would rather not read the table 12 times. Is there a way to do this just reading through the table once? My Hyperion Essbase book gives an Oracle example for swapping rows and columns by using a decode function. Is there a similar function in SQL Server?
View 2 Replies
View Related
Jul 26, 2002
Can anyone help me retrieving that result.
take an example
EXEC-ID JOB-CODE
0001 20
0001 63
0001 03
i want result in the following format:
EXEC-ID JOB-CODE1 JOB-CODE2 JOB-CODE3
0001 20 63 03
View 1 Replies
View Related
Dec 29, 2014
Came across one scenario not able to find out how to do it..
Below is the table data
ColA
1
-1
2
-2
Need output in below way
ColA ColB
1 -1
2 -2
View 5 Replies
View Related
Apr 10, 2008
Hi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )
View 8 Replies
View Related
Apr 26, 2008
i have 2 columns like
Col1====col2
username=======saif====mr@hotmai.com
email=======saif@hotmail.com
name======saifullah
I want to get these records from sql like
username email name
saif saif@hotmail.com saifullah
________________________________-
how can I achive this
Muhammad Saifullah
View 3 Replies
View Related
May 20, 2008
i want to split the value of one column into multiple rows. based on comp capacity. pl help me in writing query
Actual table
Product IdProductQtyCompCapacity
1 8000 5000
2 10000 5000
4000
4000
Resultant table
Product IdProductQtyCompCapacity
2 50005000
2 50005000
1 40004000
140004000
View 6 Replies
View Related
Aug 24, 2013
I have a table with following sample data:
**CategoriesIDs RegistrantID**
47 1
276|275|278|274|277 4
276|275|278|274|277 16261
NULL 16262
NULL 16264
NULL 16265
[code]....
I need to know :
1). which category has how many regisrtantids (like 277 has how many registrantids) 2). group the registrants by category so that i can find which registrants are in category 277 for example)
Do I need to create a function which generates a table from csv ? I have created a function but not sure if it will work in this situation with IN clause.
View 2 Replies
View Related
Jul 2, 2014
I am looking to transpose a row into multiple columns. I have a member's data who might be associated with multiple labs for that one member. Instead of having multiple records for that member, I would like to have one record per member with multiple rows for the lab data.
View 16 Replies
View Related
Mar 16, 2006
Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.
I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.
So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.
This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;
And this is what I have been able to salvage into MSSQL format:
SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)
How can I total the three columns and add the tax?
View 3 Replies
View Related
Jun 5, 2006
Mike writes "Hi,
I am a beginner with TSQL and I hope this is not a silly question :-)
Lets say I have a table with 2 columns, 1 a primary key identity field with increment 1 and the other a char.
EG:
ID ANIMAL
---------
1 CAT
2 DOG
3 PIG
4 RAT
5 PIG
6 DOG
7 DOG
.
.
And so on with many entries
How do I return a selection of rows that have the contents of the ANIMAL field matching 1 or more times
EG:
From above table I want to return rows 2,6 & 7 and 3 & 5 ONLY and not 1(CAT) and 4(RAT) because they only occur once.
In my real life situation I have unknown numeric data in field 2 but the principal is the same.
How do I do this?
Thanks in Advance
Mike"
View 2 Replies
View Related
Jul 23, 2005
I am struggling on this issue and was hoping if anyone out there canhelp me.Here is the setup: I have a table with the following data:TableName: TranDetailMerchID ItemName Price------------------101 A 5101 B 3.5101 C 0102 B 7.6102 C 4102 E 65102 G 4103 K 35Table DesignMerchID intItemName varchar(50)Price floatWhat I would like is a report that looks like thisMerchID A B C E G K101 5 3.5 0102 7.6 4 65 4103 35This report can change on every run depending on data in tableTranDetail. The column name in report depends on ItemName in tableTranDetail. As seen in the above report, there is no data for Items D,F, H, I, J and hence they do not show up in the report.What I need: Code for a stored procedure that can get me this data.Thanks for your help...DBA in despair!
View 7 Replies
View Related
Dec 21, 2005
I have a column that has text delimited by a percent sign that I wishto turn into rows.Example:A column contains ROBERT%CAMARDA, I want to turn that into two rows,one row with ROBERT and antoher row with CAMARDA.I will have source rows that have zero, one, or many percent signdelimiters that will correspond to that many rows (One percent signwill create 2 rows, 2 percent signs will create 3 rows and so forth).Any thoughts?TIARob
View 1 Replies
View Related
May 29, 2007
Hi Friends,How to transpose the columns into rowsi want to convert the table which looks like thisID Name HomePhone WorkPhone Email1 test1 678364 643733 Join Bytes!2 test2 678344 643553 Join Bytes!to a table which should look like thisID Name Device1 test1 6783641 test1 6437331 test1 Join Bytes!2 test2 6783442 test2 6435532 test2 Join Bytes!Thanks in AdvanceArunkumar
View 1 Replies
View Related