Select Only The First Qualifying Record In SQL Express
Jan 13, 2006
Not sure if my description was accurate enough. Here is what I am trying to do. I am learning ASP.Net and am playing around with adding photo albums to my site. I would like a page listing all of the photo albums in a details view. For each album, I want to list the album name and then show a thumbnail of the FIRST photo in that album. When the user clicks on the thumbnail, he will be taken to the page showing all pictures in the album.
My problem is coming up with the SELECT statement to return each album and ONLY the first photo in each album. I have 2 tables: albums and photos. Albums has albumid, ownerid and albumname. Photos has photoid, albumid, photoname, and photothumburl.
I know I want to SELECT albums.albumid, albums.ownerid, albums.albumname, photo.photoid, photo.photothumburl FROM (I am going to mess up this syntax because I am not in front of my dev computer) albums INNER JOIN photos ON albums.albumid = photos.albumid....
But I do not know how to tell the SELECT to return only the photoid's that are the FIRST photo in the album. I can get it to work if I only use the photos table and just SELECT albumid, MIN(photoid) FROM Photos, but once I add in all the other items I want the query to return (album name, ownerid, photothumburl), I have to put them all as "Group By" and the resulting group of records I get includes all photos regardless of whether they are the first photo in the album or not. Hopefully that makes sense to people. I really would appreciate any help you can give me. So far this stuff is really fun...
I am using table variables instead of creating a temp table because it seems to be faster
But now I need qualify the table variable so I can join it with another table having a field with same name of a field from the table variable. U know if I can do that?
ex: with temp table
create table #tmp.... (F1...) #tmp.f1
with table variable
declare @temp table(... @table.f1 - can´t do it
the first question is if I can join the table variable with another table and how to do that qualifying the variable table, that is, putting the name of the var temp with the field, because the other table has a field with same name
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.
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?
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?
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.
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.
how to select record from the table where the data between a range. example between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks
I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records)
I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.
name, totmarks ----------------- kumar 145 ravi 300 jude 189 geeetha 320 rajesh 251 guru 190 ramesh 99 ----------------------
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db. 00:00:00 01:00:00 02:00:00 ... 21:00:00 22:00:00 23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one. SELECT TimeOfCapture, Value1, Value2, Value3 FROM FactoryTable WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.
Example : ======= Details table is as follows HeaderID DetailsID 1 1 1 2 1 3 2 4 3 5 3 6 3 7 3 8
SELECT H.TransDate, H.SupplierName, D.DetailsID FROM Header H, Details D WHERE H.HeaderID = D.DetailsID AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)
As I am dealing with very huge data it is taking very long time to execute.
because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result
select 'PURCHASE' as EntityName, d.PartnerName, h.* from ( select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId from DataImport.PurchaseHistory group by DistributorId, StartDate, EndDate ) h inner join Partner d on d.PartnerId = h.DistributorId where d.Active = 1 order by DistributorId, StartDate desc, EndDate desc
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
All columns are strings as the data is obtained from text file. Column F3 can have same or different dates. In the above data select 1st record as it is the most recent. If data is like - F1F2F3 F4 20016395074/7/2004 15:40:55 23516395074/7/2004 19:31:54
I have view with Patients name and Appointment table where I save those patients. In form I have 2 comboboxes. For PatientComboBox source is store procedure based on Patient view. For PatientAppintmentComboBox I would like to create store procedure.
How to create store procedure to display only one PatierntName record in PatientAppointmentComboBox if Patient_Id selected from PatientComboBox exist or not exist in Appointment?
I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?
Hi there, it has been a while since i have posted. I am in asituation where I am stumped. I am learning to build a dts packagewhere I am connecting to a table in an AS400. This database is beingmaintained by an outsourced company and therefore I can't change thetable structure or even ask them to. Anyway, this table currently hasabout 104,000 records. I am building a package to check it and pullout the most recent records and put them where they go in my SQLServer 2000 tables. The only way I can think of to get the mostrecent records is to use a global variable in the package to rememberthe record count and then get those records from that record positionon. Problem is, I have no idea how I would go about selecting recordsfrom a record position. Does anybody have any ideas or should I beusing a different approach? There are no time stamps to work from. Iwas told that the AS400 records, including updates, are appended tothe table, which is why I thought this approach made sense. I wouldtruly appreciate any help.
I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
Hi!I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.Can I do that?/Johan Ch
