Get First Occurrence Of A Row

May 7, 2008

Here's my table:

-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------

Users can make several different orders. I want to only select the row with the first occurrence of a userID. For example:

-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
1 | 3.00 | Box
1 | 6.00 | Candy
2 | 7.00 | Towel
3 | 5.00 | Flower
3 | 6.00 | Candy

Should only select:
1 | 3.00 | Box
2 | 7.00 | Towel
3 | 5.00 | Flower

View 8 Replies


ADVERTISEMENT

First Occurrence From 1 To Many Relation

May 12, 2006

This one shouldn't be too hard to do, just can't seem to figure it out.

I have two tables
tNames which contains 'ID' and 'Name'
tLocations which contains 'ID' and 'Location'

each ID may be associated with many Locations

I want to run a query to display these results:
[ID, Name, Location]

but only to return the first Location associated with that ID (or the max, min, I don't really care as long as it's only one result)

right now I have

Code:


SELECT tNames.ID, tNames.Name, tLocation.Location
FROM tNames INNER JOIN tLocations
ON tNames.ID = tLocations.ID



but this returns a new row for each different Location of the same ID

Thanks.

View 3 Replies View Related

How To Extract Row Corresponding To First Occurrence Of ID

Mar 4, 2014

I have a sample table as follows:

IDITEM_EFFECTIVE_DTITEM_TERMAMOUNT
106738118-Jan-142141-200
106738102-Feb-142141-274.82
108766810-Jan-1421411238
108766810-Jan-142141-1238
108766810-Jan-142141309.5
108766810-Jan-142141-309.5
109754020-Jan-142141-3814.16
109754025-Feb-142141-100
110337727-Feb-142141-200.6
110337731-Jan-142141-19.24

I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.

1067381, 1087668, 109754, 1103377.

How does one handle this. I have not been able to come up with the sql yet.

View 4 Replies View Related

Nth Occurrence Of Character

Mar 6, 2007

I have a field that contains data like "mary;john;dog;cat;frog" I want to query the field for the values between each of the semi colins. I was going to use Substring with a patIndex but I can only patIndex the first semi colin and not the 2nd 3rd.... I was wondering if someone could tell me the right was of doing this or point me in the right direction.

Thank you,

View 1 Replies View Related

Finding Occurrence That Does Not Exist

May 15, 2012

In the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don't contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2...

use LEIS
If OBJECT_ID ('dbo.temp') IS NOT NULL
DROP TABLE dbo.temp
GO
CREATE TABLE dbo.temp
( OrderNo bigint not null,
OrderNoindicator tinyint not null

[Code] .....

View 14 Replies View Related

Retrieving The The Max Occurrence Of A Record

Jul 20, 2005

Hi,In the datawarehouse DB (under MS commerce server 2002) a table storesthe referer domain name. Table structure is likerefererdomainid <binary>,domainInternalFlag<0/1>,refererDomainName<varchar>e.g.<binary>|0|unknown<binary>|1|google.com<binary>|1|yahoo.com<binary>|1|google.com<binary>|1|google.com<binary>|1|google.com<binary>|1|altavista.commy problem is to build a query (using this table only) whichrefererDomainName has the max occurrence and how many times. As in thetable above it is google.com and 4 times.Can anyone help me.Thanks in advance.

View 1 Replies View Related

How To Get The First Occurrence Of A Record From A Table?

May 21, 2007

I have the following query:
SELECT Notifications.[TimeStamp])
FROM dbo.vwGrantsMaster LEFT OUTER JOIN
dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId
It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.
What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.
Any help?
Thank you in advance.

View 3 Replies View Related

How To Locate Last Occurrence Of Character In String?

Sep 18, 2001

How to locate last occurrence of character in string?

View 1 Replies View Related

Finding Last Occurrence Of A Character In A String?

Jun 25, 2002

I have a problem finding the last occurrence of a character in a string.

The charindex seems just able to search from left to right..

View 4 Replies View Related

T-SQL (SS2K8) :: First Occurrence Of The String From Text

Sep 4, 2015

I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.

TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'

From the above text1, I want to get “AXNARENDR”.

Based on logic defined below:

First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”

In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string

i.e AXNARENDR (TEXT1 I defined “A” with 4th occur

TEXT 2:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'

From the above text2, I want to get “AYENDGHR”.

TEXT 3:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'

From the above text3, I want to get “AZENIVKHR”.

View 9 Replies View Related

SQL Server 2012 :: Get First Occurrence Value When Value Is Duplicated

Oct 16, 2015

generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column

IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int

[code]...

View 3 Replies View Related

How To Count Number Of Value Occurrence In Column

Oct 24, 2013

I am having following data:

c1c2c3
122401
1221072
122833
122793
122813
122314

[code]....

I want to count number of occurrence in column c3. i.e., count 1 occurred value 9 times, count 2 occurred value 3 times, count 3 occurred value 3 times.

Output
c1p1p2p3
122933

View 6 Replies View Related

Finding Mode (frequent Occurrence)

Jun 5, 2007

What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!

View 5 Replies View Related

Need Help To Take The Variables Associated With The 1st Occurrence Of A Date Variable

Mar 8, 2006

Hi,I am trying to join two tables, one has multiple records per ID, theother is unique record per ID.The two tables look like belowAID date var1 var 2001 1/1 10 20001 2/1 12 15001 3/1 17 18002 2/1 13 10002 3/1 12 14............BID001002003004....The join conditions are1. table A's ID = table B's ID2. take the variables associated with the 1st occrrence of the datevariable in table A'sI have the following SQL code but the it didn't work. It says thecolumns are ambiguously defined. Anyone can help me? Greatly appreciateit!PROC SQL;CONNECT TO ORACLE (USER="&user" PASS="&pass" PATH="@POWH17"BUFFSIZE=25000);CREATE TABLE actvy1_1st AS SELECT * FROM CONNECTION TO ORACLE(SELECTactvy1.ID,actvy1.var1,actvy1.var2,actvy1.datefromA actvy1,(select a.ID,min(a.date) mindatefrom A a,B cwhere a.ID =c.IDgroup by ID) bwhere actvy1.ID =b.IDand actvy1.date =b.mindateorder by ID);disconnect from oracle;quit;

View 3 Replies View Related

Reporting Services :: SSS Highlighting Row If More Than 1 Occurrence

Sep 2, 2015

I have a weekly report with multiple products due for manufacture on the same day. viz. - 

02/09/15 Product A
02/09/15 Product B
03/09/15 Product A
04/09/15 Product C
04/09/15 Product X

I need to highlight any product that appears more than once in the report. e.g. Product A.

View 7 Replies View Related

Power Pivot :: DAX - Last Occurrence In A Month

May 27, 2015

I have [income]- (REVENUE) table of accounts, each account can have multiple instances for one month. how can I find the last occurrence of all the accounts in a month (and then summarize all of these latest occurrences)?

account revenue revenue_date
1 100 2010-01-01
1 200 2010-02-03
1 300 2010-02-04
1 1000 2010-06-04
2 100 2010-02-01
2 200 2010-03-03
2 1300 2010-07-04
2 11000 2010-08-04
3 2100 2010-01-01
3 2200 2011-02-03
3 300 2011-02-04
3 221000 2011-06-04
4 222 2010-02-01
4 2222 2012-03-03

View 10 Replies View Related

Syntax To Abandon SELECT On First Occurrence Of Criteria?

Nov 6, 2013

Have a need to scan a large table to see if a set of criteria have ever been met.

If/when the scan hits its first record meeting the criteria, the scan can be abandoned.

Is there some syntax/option that accomplishes this?

Right now, I am doing a SELECT with criteria against the table and the @@ROWCOUNT gives me a zero, or non-zero value.

But that methodology means that the SELECT has to execute against the entirety of the table.

I'd like to abandon the SELECT as soon as it detects a first record meeting the criteria.

View 3 Replies View Related

SQL Server 2012 :: Get Latest Occurrence Of X Month?

Jan 17, 2014

I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.

At the moment I'm using this:

DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')

View 8 Replies View Related

T-SQL (SS2K8) :: Find Occurrence And Remove String?

Mar 19, 2014

My sample data is as below.

plan type A change from plan type B from Plan type C

Insurance plan M changed from Insurance plan b From plan d from Plan N

Now from above strings i want to remove all data from second appearance of from. i.e. i want display values as below.

plan type A change from plan type B

Insurance plan M changed from Insurance plan b

View 3 Replies View Related

T-SQL (SS2K8) :: How To Find Saturday Occurrence Between Two Dates

Jun 17, 2014

I'm trying to find if the Saturdays worked in Bi-Weekly period is first or second, based on the Saturday occurrence the CODEID column value changes from 01 to another #, for example if it's first Saturday then CODEID changes from 01 to 02 and it's second Saturday then the CODEID changes from 01 to 03.

Below is my table, current result and desired results.

My Table:

WITH SampleData (PERSON,[HOURS],[RATE],[CODEID],[DOW],[DATE]) AS
(
SELECT 1234,7.00,40.00,01,'Thursday','05/01/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Friday','05/02/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Saturday','05/03/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Monday','05/05/2014'
UNION ALL SELECT 1234,8.0,40.0,01,'Tuesday','05/06/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Wednesday','05/07/2014'
UNION ALL SELECT 1234,3.5,40.0,01,'Thursday','05/08/2014'
UNION ALL SELECT 1234,7.0,40.0,01,'Friday','05/09/2014'
UNION ALL SELECT 1234,3.0,40.0,01,'Saturday','05/10/2014'
)
SELECT * FROM SampleData

Current Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0001Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0001Saturday05/10/2014

Expected Results

PERSONHOURSRATECODEIDDOW DATE
12347.0040.0001Thursday05/01/2014
12348.0040.0001Friday 05/02/2014
12343.5040.0002Saturday05/03/2014
12343.5040.0001Monday 05/05/2014
12348.0040.0001Tuesday 05/06/2014
12343.5040.0001Wednesday05/07/2014
12343.5040.0001Thursday05/08/2014
12347.0040.0001Friday 05/09/2014
12343.0040.0003Saturday05/10/2014

View 9 Replies View Related

Creating SELECT Query Which Involves Getting FIRST Occurrence

Apr 25, 2014

I have a problem creating a SELECT query which involves getting the FIRST occurrence, the SUMS and the LAST occurrence of a selected row. I have two tables A and B which has the following data:

Table A

----------------------------------------------------------------
|Item ID | Item Name | Item Description | Current Quantity |
----------------------------------------------------------------
----1------------Bolts-------------35"----------------20
----2-----------Paint----------GOLD Paint------------30
----3-----------Screws------------30"----------------40
----------------------------------------------------------------

and Table B

--------------------------------------------------------------
|Log ID | Item ID | Beginning | Add | Less | Ending | Date |
--------------------------------------------------------------
---1---------1----------30-------0-----10------20------04-04
---2---------2----------40-------10----10------40------04-04
---3---------3----------50-------5-----10------45------04-04
---4---------1----------20-------0-----0-------20------04-05
---5---------2----------40-------0-----10------30------04-04
---6---------3----------45-------0-----5-------40------04-04
-------------------------------------------------------------

and I have two datetime pickers, one FROM date picker and a TO date picker If a user selects FROM 04-04 TO 04-05...I want a select statement which will select the Item Name and Desc, the BEGINNING QUANTITY (in reference to the FROM DATE), the SUM of Adds and Less and the ENDING QUANTITY (in reference to the To Date) and then group by their Item ID

In this case, it will generate a table like this

----------------------------------------------------------
Item Name | Item Desc | Beginning | Add | Less | Ending |
-----------------------------------------------------------
--Bolts---------35"---------30---------0-----10-----20
--Paint-----Gold Paint------40---------10----20-----30
--Screws-------30"---------50---------5-----15-----40

"SELECT A.Item Name, A,Item Desc, B.Beginning=(select B.Beginning from Table B where A.Item ID = B.Item ID AND Date = From Date), SUM(B.ADD), SUM(B.LESS), B.Ending=(select B.Ending from Table B where A.Item ID = B.Item ID and Date = To Date) WHERE A.Item ID = B.Item ID and Date(B.Date)>From Date AND Date(B.Date)< To Date group by B.Item ID order by A.Item Name, Item Desc"

My query returns the right Item Name, Desc, the Sum of Add and Less.My problem is that I can't get the value of my beginning and ending.I think I have a problem in A.Item ID = B.Item ID. As I tried a fixed value condition, (B.Item ID = 2), it will get the right result), I'm guessing the program is not getting A.Item ID.

View 1 Replies View Related

Find The Occurrence Of A Particular Character Inside A String

Dec 5, 2007

Hi all,

Am searching for a built-in function that retrives me the occurrence (counts) of a particular character inside a string.


For e.g. In the String 'SqlServer'. I want to find the no of the character 'e', which shud retrive the value as 2.

Can this be acheived through any function.

Cheers !
ash

View 6 Replies View Related

T-SQL (SS2K8) :: String Occurrence Count With Cross Apply

Jun 17, 2014

See sample data below. I'm trying to count the number of occurrences of strings stored in table @word without a while loop.

DECLARE @t TABLE (Id INT IDENTITY(1,1), String VARCHAR(MAX))

INSERT INTO @t
SELECT 'There are a lot of Multidimensional Expressions (MDX) resources available' AS String UNION ALL
SELECT 'but most teaching aids out there are geared towards professionals with cube development experience' UNION ALL

[Code] .....

View 9 Replies View Related

Does Full-text Search Could Count Words Occurrence

May 15, 2008

Hello,
I use the full-text search utility in SQL Server 2005 to find word in PDFs document.
This is my 'Documents' table:

id (PK), data (VarBinary(max)), extension (nvarchar(4))

My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.

SELECT * FROM Documents WHERE freetext([data], 'Microsoft');
1 , 0x255044...., .pdf

But I need to know how many times 'Microsoft' word appears in this document.
Do you have any idea how can I retrieve this information?
Thanks in advance!

View 3 Replies View Related

Does Full-text Search Could Count Words Occurrence Number

May 15, 2008

Hello,
I use the full-text search utility in SQL Server 2005 to find word in PDFs document.
This is my 'Documents' table:
id (PK), data (VarBinary(max)), extension (nvarchar(4))
My full-text catalog on 'data' column works fine because when I search 'Microsoft', my document containing this word is returned as result.

SELECT * FROM Documents WHERE freetext([data], 'Microsoft');
1 | 0x255044.... | .pdf

But I need to know how many times 'Microsoft' word appears in this document.
Do you have any idea how can I retrieve this information?
Thanks in advance!

View 1 Replies View Related

SQL Server 2008 :: Finding Multiple Character Occurrence In String

Mar 24, 2015

I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.

This is for a data cleanup.

CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')

SELECT * FROM #Names
DROP TABLE #Names

View 9 Replies View Related

T-SQL (SS2K8) :: Obtain A Record Set That Returns Just First Occurrence Of ItemNo Field

Jun 9, 2015

Here is a CTE query

With mstrTable(ItemNo, Sales)
as (
Query1
Union All
Query2
)
Select Row_Number () Over(Partition by ItemNo order by ItemNo)as RowNo, ItemNo, Sales
From mstrTable
order by ItemNo

The results from Query1 and Query2 overlap sometimes.

The result set looks like:

1 Item1 10000
2 Item1 10000
1 Item2 20000
1 Item3 30000

I only want the first occurrence of each item. The desired result set is:

1 Item1 10000
1 Item2 20000
1 Item3 30000

I am not able to add a "Where RowNo = 1" to the query. SQL returns an "invalid field name".How would I obtain a record set that returns just the first occurrence of the ItemNo field?

View 7 Replies View Related

Count Of Occurrence In Given Period Of Time - Query Has Unexpected Results

Sep 12, 2013

I need to query the count of an occurrence in a given period of time, so I created this query. But it does not give me any results.

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
GROUP BY x.[StreeLine1], o.ID, o.TimeOfOrder
HAVING COUNT(x.[StreeLine1])>1

Then I change the query slightly and I ask it to show me the ones that are going to '599 Ships Landing Way' and it gives me 356 results! The Query doesn't crash, but it doesn't give me the results I need. What did I do incorrectly?

SELECT TOP 1000
o.[ID]
,o.[TimeOfOrder]
,x.[StreeLine1]
FROM [SC].[dbo].[bvc_Order] o
FULL JOIN SC.dbo.xmlAddressRead x
ON o.ID= x.id
WHERE DATEDIFF(HOUR,o.[TimeOfOrder],(DATEADD(Hour, -48, GETDATE()))) < 48
AND x.[StreeLine1]='599 Ships Landing Way'

I use Microsoft SQL 2008

View 12 Replies View Related

Counting Occurrence Of A Value &&amp; How To Format For Fixed Number Of Rows &&amp; Columns

Jan 7, 2008

Hi,

I need to count and display the number of records which have GradeTitle="SHO". I'm only starting to use BI development studio and all attempts at using the built in aggregate functions have failed.

Also, the report I wish to create has a fixed number of columns and a fixed number of rows as the info being displayed is really only counting values in the DB. I tried using Table but multiple rows were created.

I'd appreciate if anyone could point me in the right direction, as searching this forum turned out to be pretty fruitless for me.

Thanks in advance,
John

View 16 Replies View Related

Power Pivot :: Find Percent Of One Of Last Occurrence Compared To All Last Occurrences (DAX)

Jun 3, 2015

I have a fact -REVENUE  table of accounts, each account can have multiple instances . And I have an Aggregate that summarize the latest occurrence of a revenue for each account ( in a chosen Period )   : 

AccountLastRevenue:=SUMX( 
VALUES('scd_FactAccountRevenue'[Account]),
    CALCULATE(
        SUM('scd_FactAccountRevenue'[Revenue]),
        LASTDATE('scd_FactAccountRevenue'[Revenue_Date])   ) 
)

How can I find the percent of one of the lastest accounts Compared to all accounts? Assuming i have connect Dim_Time ( Y-Q-M-D )  to Revenue_Date, how can i find the percent of one Month Compared to all the months in the Quarter ( And so on hierarchies ) ?

scd_FactAccountRevenue:

powerpivot :

View 11 Replies View Related

SQL Server 2012 :: Query - Counting Item Occurrence Over A Rolling 72 Hour Period

Jun 18, 2015

I am using MS SQL 2012 and have a pretty simple table dbo. Migration Breakdown with sample data as follows.

DepartDateTime ZoneMovement
2015-06-26 14:00:00.000 6 to 4
2015-06-26 14:00:00.000 11 to 7
2015-06-26 15:30:00.000 9 to 6
2015-06-26 21:00:00.000 7 to 3
2015-06-27 08:01:00.000 7 to 4

[code]....

What I am trying to do is parse the data set to find out when we have more than three like movements ex. 3 to 10 within ANY rolling 72 hour period. I have looked at the SQL Window Functions OVER with a ROW | RANGE subclause, but I can't find out how to tackle this rolling 72 hour business.

View 9 Replies View Related

Whats Does This Mean ??? --- SQL Server Has Encountered 1964 Occurrence(s) Of IO Requests Taking Longer Than 15 Seconds

Sep 13, 2007

Hello,

I see the following error on the sql server log of one of our sql servers running sql 2000 with sp4.
SQL Server has encountered 1964 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [h: empdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000534. The offset of the latest long IO is: 0x0000002b09e000


Any idea as to what might be causing this error. Appreciate any comments.

Thanks!

View 3 Replies View Related







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