Retrieving Only One Record Per Item Using A Select

Apr 7, 2008

The following select retrieves multiple reoords for each i.number. How can I select just the first record for each i.number?

SELECT i.number, i.desc, i.it_sdate, v.entry_date FROM itemsnum as I INNER JOIN Inventor as V ON SUBSTR(i.number,1,5)=v.catalog WHERE v.entry_date<ctod("04/01/06") AND i.it_sdate < ctod("04/01/06") order by number, it_sdate desc

Thanks in advance!

View 5 Replies


ADVERTISEMENT

Retrieving Multiple Rows For A Cursor Item

Sep 28, 2000

Hi, can someone plz give me an idea of how to proceed with this...
I've got a server side cursor that retrieves a list of customers from a sql server table which match a specific criteria (those who have had orders in the yr 2000)
For each customer, I need to retrieve a list of 5 top items purchased along with dollars spent on each item by that customer.
I've tried to do this in a loop that uses a counter but probably my syntax was off and I'm not sure that this is better than a correlated join? Can someone show me a temlate of the appropriate syntax to use for this operation
I appreciate suggestions, thanks again.

Irene

View 2 Replies View Related

How To Select The Last Identical Item# Until A Different Item# In The Table

Jul 12, 2007

Hello,Basically, I have a table with 2 fieldsId     item#1      33332      33333      22224      22225      22226      33337      33338      3333I would like to only select the last identical Item# which in this case would be  the id 6,7 and 8Any idea how could I do that?Thanks

View 1 Replies View Related

Find Most Recent Record For Each Item?

Oct 29, 2014

We have a work order notes table in our ERP system, and I want to see the most recent note record for each work order. Sometimes there will one be one, so I want to see all those, but sometimes there will be several notes for each work order and in this case I want to see only the most recently added note for that work order.

The query below shows some results as an example. In this case I want to see all the records except for work order number DN-000023 where I only want to see the note dated/timed 07-12-2011 16:52 (the most recent one).

select id, worknumber, date, notes from worksordernotes

id worknumber date
----------- ------------ ----------------------- --------------------
1 DN-000056 2011-12-07 13:22:00 13.20 PM JAMES- SPOK
2 DN-000079 2011-12-07 14:24:00 JCB HAVE TOLD ME THE
4 DN-000065 2011-12-07 15:48:00 ANDY FROM SITE RANG
5 DN-000023 2011-12-07 15:54:00 CHASED THIS 4 TIMES
6 DN-000023 2011-12-07 16:52:00 HOLTS ATTENDED THIS
7 DN-000092 2011-12-08 09:50:00 RETURNING WITH PARTS

View 3 Replies View Related

Reporting Services :: SSRS Record Counts For Group Item

Aug 11, 2015

I have a table where I am grouping on one field and would like an individual (separate) count of values from another field of same table.  So for example, I have following data:

instance_id,  area,        values
101              North       1
102              North       2
103             East          2
104             East          2

I would like to report on Area, and count of rows with different Values types, for example:

Area                            Value - 1,    Value - 2,  Value - 3
North                                 1               1              0
East                                   0             2                0

I am not sure what the technical term is for such report, but I can group by Area column, and but its aggregating counts on different Value types that I am having difficulty in performing in SSRS.

View 2 Replies View Related

Record Retrieving Problem

May 11, 2007

hi all,
I have a table productprice which has the following feildsid             price                     datecreated                  productname 1               12.00                    13/05/2007                        a1   2              23.00                     14/05/2007                       a13               24.00                     15/05/2007                        a14               56.00                     13/05/2007                            b15               34.00                     18/05/2007                            b16               23.00                      21/05/2007                           b17               11.00                       12/02/2007                    c1                   8               78.00                        12/03/2007                  c2I
need to select the rows that are highlighted here.. ie the row that has
the max(datecreated) for  all the  productname  in the table..
plz help thanks in advance..

View 3 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

Best Practice For Retrieving Last Record

Apr 1, 2006

Hi there, very sorry if this is the wrong forum to post this in.

I want to know what is the BEST practice, the correct Microsoft way of doing this:

basically, lets say I am inserting a new record into SQL. simple customer record:

firstname

lastname

address

city

postcode

password

dateOfRegistration (SQL has this value and the default value is the getdate())



That's all very well. I want to know how I can get the recordID for this and return that back from the caller (returning is easily done) -

You cannot really after this insertion, perform a SELECT statement to get the LAST record entered, as there maybe several records that could all be inserted at the same time by coincidence. It's not the best way of going about this.



I want to know what is the best way of getting the just inserted record's recordID - I was thinking about using date and time, manually inputting them and then using that to retrieve the last record/current inserting record but again its not the best way of going about doing this.



what is the best way?



Many thanks for your help!

View 12 Replies View Related

Retrieving 1st Record In Time Range Else

Jan 5, 2014

I have a date/time value (dd/MM/yyyy HH:mm:ss) stored in a field as part of each record in a dataset.

I would like to achieve the following via a single T-SQL query if possible (efficiency is not an issue as the dataset is small)

On query run;

Check if any records in the dataset are within 10 minutes + or - of the current system time (using the date/time value referenced above)

if none

Load the first record

View 6 Replies View Related

Retrieving Records Within An Index Range, The Nth Record?

Mar 5, 2007

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?Possible? How can I do it?When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?I'm using SQL 2005 Express, not SQL 2000.

View 14 Replies View Related

Receiving System Error When Retrieving Database Record With Null Value

Sep 26, 2007

I have some VB.NET code to retrieve data from an SQL Server database and display it.  The code is as follows: -------------------------------------------------------------------------------------------------------
sw_calendar = calendarAdapter.GetEventByID(cid)
If sw_calendar.Rows.Count > 0 Then

lblStartDateText.Text = sw_calendar(0).eventStartDate
lblEndDateText.Text = sw_calendar(0).eventEndDate
lblTitleText.Text = sw_calendar(0).title
lblLocationText.Text = sw_calendar(0).location
lblDescriptionText.Text = sw_calendar(0).description
Else

lblStartDateText.Text = "*** Not Found ***"
lblEndDateText.Text = "*** Not Found ***"
lblTitleText.Text = "*** Not Found ***"
lblLocationText.Text = "*** Not Found ***"
lblDescriptionText.Text = "*** Not Found ***"
End If
 -------------------------------------------------------------------------------------------------------
If all of the fields in the database has values, everything works ok.  However, if the title, location or description fields have a null value, I receive the following error message:
Unable to cast object of type 'System.DBNull' to type 'System.String'.
I've tried a bunch of different things such as:

Adding ".ToString" to the database field,
Seeing if the value is null:  If  sw_calendar(0).description = system.DBnull.value...
...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.
Can anyone help me with the code required to trap the null within the code example I've provided?  I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline )
 
Thanks,
Brad

View 6 Replies View Related

Error Retrieving A Record Based On 3 Parameters. Need Help With Forming My SQL Statement

May 18, 2008

I am having trouble returning the correct record with my stored procedure.
my problem is that i don't know how to structure the sql statement to do the following:
given a set of records that have the same loankey, i need to
1. find the record that has most recent date (lockExprDt)
2. for all records with that date, find the highest Lock Number (LockNo)
3. for the all the records with that date and that LockNo, find the highest extension number (Ext) 
currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number.
any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx!
select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM'  THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10)   END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM'  THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt'  , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days' 
from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt
where a.loankey = @LoanKey

View 6 Replies View Related

Select .... Where The Item.ID Is In This Array?

Jul 20, 2005

Hiya,I want to populate a list view (in a variety of ways), but I would rathernot execute my function once for every record I want to show in the list.What I want to do is pass an array of "keys" into a stored procedure andhave that procedure return one recordset with all of the records in. Sowhat I want to do is something like:CREATE PROCEDURE MySuperProcedure@MyArrayOfIDs (some way of specifying an array of integers),ASSELECT *FROM MyTableWHERE MyArrayOfIDs.ContainsTheInteger ( MyTable.ID )Obviously this syntax is not available. Is there another way of doing this?

View 3 Replies View Related

Removing &&<Select A Value&&> Item From The Singlevalued Parameter Dropdown

May 31, 2007

Hello All,



I am developing reports using SQL Reporting Services 2005. These reports have some single valued parameters and these parameters will be provided in report viewer itself. These parameters should have "All" as default.
"Select All" comes by default in Multivalue report parameter. But for single value parameter it always shows "<Select a value>" and if the user does not select a value,it prompts for one.I need to remove <Select Value> and show "All" as default thereby user does not have to select a value.



I can include "All" as a part of the select query in the parameter's datasource so that it can be shown in the parameter dropdown . However, I am not sure how I can remove "<Select a value>" from the dropdown.


Does anyone have any pointers? Thanks in advance.









Ashish

View 3 Replies View Related

Dropdown - Multi-select Won't Show Values If Only One Item Is Available

Dec 13, 2006

I have a report that has 2 dropdowns, selecting from the first dropdown populates the second one. This works fine in the BI Studio.

When I deploy this report to the 'Report Manager' and make a selection from the first dropdown, the second dropdown loads (as expected). I tried to select from the second dropdown (which has only 1 item - which is correct), the dropdown does not appear correctly - as in, I can't see that item.

Since we can't attach anything here, below is the link to a screenshot of my issue:
http://docs.google.com/View?docid=ddd6j2xn_52c5qd5

If you look closely at the screenshot from the link above, you'll see that there is a value in the second dropdown - it just won't show completely - as if the dropdown is not rendering correctly. I can view source on the page and see that the dropdown has a value.

What appears to be happening is the if only 1 item is in the second dropdown and that item is longer than the size of the dropdown, the dropdown won't render.

Here is my value for the second dropdown '012 Candy Gadler David Thapero'. This is the only value in the second dropdown. - You can try 35 chars or more in the dropdown to confirm.

Notes:
+ No special chars are in either dropdown
+ I am using IE 7 (Also had someone test this on IE 6 - same problem)
+ Using Visual Studio 2005 to build report - where this works fine

Work around:
+ If I add another item to the dropdown via a UNION query, I see the original value + the new item in the dropdown #2


Please share your thoughts
Thanks,
h.


 

View 9 Replies View Related

Select Most Recently Edited Item AND A Certain Type If Another Doesn't Exist

Sep 20, 2007

I've got a big problem that I'm trying to figure out:I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.I don't need this for an individual ID select, I need it applied to every record from the table.My address table has some columns that look like:
[AddressID] [int][LocationID] [int][Type] [nvarchar](10)[Address] [varchar](50)[City] [varchar](50)[State] [char](2)[Zip] [varchar](5)[AddDate] [datetime][EditDate] [datetime]AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.How can I do this efficiently with perfomance in mind???Thank you in advance for any and all replies...

View 5 Replies View Related

Select Most Recently Edited Item AND A Certain Type If Another Doesn't Exist

Sep 20, 2007

I've got a big problem that I'm trying to figure out:

I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.

I don't need this for an individual ID select, I need it applied to every record from the table.

My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]

AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.

How can I do this efficiently with perfomance in mind???

Thank you in advance for any and all replies...

View 2 Replies View Related

Create A Query Where Semi Item Materials Are Also Listed In Finished Item Recipe?

Dec 6, 2013

I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.

View 5 Replies View Related

Select Statement Where One Of Columns Assigns Sequential Value To Each Row Based On Item Number

Jul 14, 2014

I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:

item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3

View 2 Replies View Related

Help, Running A Control Flow Item Does Not Kick Off The Next Item

Mar 6, 2007

Hi,

Here's my problem. I have 2 tasks defined in my Control Flow tab:

EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE

When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).

Yes, they are connected by a dark green arrow, as indicated in my diagram above.

Why is this?? Am I missing something here? Need help.

THANKS

View 3 Replies View Related

Retrieving To Local Variable From Select Top ...

Feb 29, 2008

Hi,

I€™ve got the following statement :

SELECT TOP(1) Date, Status FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC

I want to retrieve the status field to a variable, but I€™m not being able to do so€¦ I think its due to the TOP function€¦

I have tried those with no success:

SELECT @var2 = TOP(1) [Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC
SELECT @var2 = [TOP(1) Date], @var = [ Status] FROM TABLE1 WHERE TEC_STATUS = 1 ORDER BY DATE DESC


Is there Any way I can do that ?

Thanks

View 1 Replies View Related

Select Query For Retrieving Data Using 3 Tables?

Apr 21, 2015

I want to retrieve the data from table "Document" and i need to check the below condition using 3 tables.

Document.ID=Project.ID=Group.ID

Here Project and Group is an another table.

Query : Select Document.Name from Document, Group, Project where
Document.ID = Group.ID and Document.ID= Project.ID.

is this right a way?

View 3 Replies View Related

Paging (retrieving Only N Rows From A Select Query) Like LIMIT

Jun 28, 2002

Message:

Hi,

Suppose i execute a query,

Select * from emp,

I get 100 rows of result, i want to write a sql query similar to the one available in MySql database where in i can specify the starting row and number of rows of records i want,

something like,

select * from emp LIMIT 10,20

I want all the records from the 10th row to the 20th row.

This would be helpful for me to do paging in the front end , where is i can navigate to the next previous buttons and fetch the corresponding records.

I want to do something like in google, previous next screens.

So I am trying to limit the number of rows fetched from a query.

somethin like,

select * from emp where startRowNum=10 and NoOfRecords = 20

so that i can get rows from 10 to 30.

Has any1 done this, plz let me know.

Cheers,
Prashant.

View 1 Replies View Related

How To Select Description When Retrieving Data From Quote Table

Mar 11, 2014

I have 'codes' and 'Description' field in 'ecode' table and 'codes' in quote table values for 'Codes' in quote table is like

Quoteid Codes

0012 LB,WS
0031WDC

In 'ecode' table 'description' column contain the description for each code

for eg :

Ecode Table : -

Codes Description
Lb - Late Booking
WS - Winter Sports
WDC - Wedding Cover

How to select 'description' when retrieving data from quote table

View 2 Replies View Related

How Can I Create A New Custom Report Item? The Report Item Is Extends Matrix.

Jan 18, 2007

hi everyone

what the matrix's class name ?

where is the matrix's dll?

Can i create a new class extends the matrix?

I want to override the matrix's onpaint method.

Can i do this ?

why the table not have the column group?













View 9 Replies View Related

Can I Use SELECT Statement To Select First 100 Record????

Apr 21, 1999

I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?

Thanks,

Sam

View 1 Replies View Related

Select One Record Only

Nov 14, 2001

Hi

Here is my example: I have the following table

ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith

I want to select only one record of each ID, the output should be

ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543

It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.

Thanks

View 4 Replies View Related

Trying To Select Last Record...

Nov 8, 2004

I have a sql query that looks for values in a few different databases....

is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?

View 2 Replies View Related

Select One Record

Sep 18, 2013

I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?

View 3 Replies View Related

Finding Orders That Have At Least 1 Promo Item And 1 Non-promo Item

Oct 16, 2014

I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.

I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:

Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null

[Code] ....

So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.

View 6 Replies View Related

Using TOP To Select The 2nd Highest Record

Jun 19, 2007

Hai frendz,

I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-

"select top 1 EID, Salary from Employee ORDER BY Salary DESC"

Now I need to write a query which selects the second highest salary.
So how to achieve this?..

thanx

View 6 Replies View Related

How To Lock Record On Select

May 29, 2008

hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.

View 1 Replies View Related

Select Only If There's A Same Record In The Table

Apr 26, 2005

i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.

View 1 Replies View Related







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