Query To Retrieve 5 Records Immediately Before And After A Particular Record In Sorted Manner
Just consider a simple table with one integer column (however numbers are not inserted in sorted manner and some of them may be missing).
Given a particular record, say 32, I would like to retrieve 5 records immediately before and after this record in ascending manner of sort.
So, in this case I should get
25, 27, 28, 30, 31, 32, 33, 34, 37, 38, 39 (I did not have 26, 29 and 35 in the table)
Why do I go about writing such query?
View Complete Forum Thread with Replies
Related Forum Messages:
Retrieve Most Recent Records From Database Sorted By String Value
I'm having a problem with a simple select query. I've got users uploading data into a database. I want to do something that seems as if it should be really simple, but it isn't so far. Basically, I want to retrieve the latest 12 records from the table, sorted by the date-time field (uploaddate) then by a varchar field (name). Here's the SQL that I'm using: select * from tblcontent where filetypeid=1 and release='Y' order by uploaddate desc, name limit 12; And here's the table schema: Field,Type,Null,Key,Default,Extra ContentID,int(3),,PRI,NULL,auto_increment Name,varchar(50),,,, Filename,varchar(100),,,, Thumbnail,varchar(100),,,, FileTypeID,int(11),,,0, UploadDate,datetime,,,0000-00-00 00:00:00, Description,text,,,, ContributorID,int(10) unsigned,,,0, Release,enum('Y','N'),YES,,N, The problem I'm running into is that while I am retrieving the most recent 12 records, it's not sorting by the varchar field. Instead, the 'Name' field seems randomly sorted. If I change the sort order (name, uploaddate desc), I don't get the records that I want. This seems like it should be something very simple to do, but it's driving me nuts and I really have no idea how to resolve the problem. Code:
View Replies !
Trying To Retrieve Most Recent Record Per Date
I have the following query which is retrieving a set of data it is almost what I want but I can not manage to get the result I desire. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '37.500','0.334449476181','[NULL]','[NULL]','2006-07-27','15:00:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '40.812','0.8538683','0.000','0.0000000000','2006-05-12','08:50:00' '41.580','2.7517359','40.370','-0.2383940000','2006-05-12','08:45:00' '40.756','0.8155671','0.000','0.0000000000','2006-05-12','15:00:00' '37.952','-0.472445','0.000','0.0000000000','2006-05-12','14:53:18' '38.010','-0.3203424','0.000','0.0000000000','2006-05-12','14:52:33' '39.488','3.3474615','37.916','-0.7667557000','2006-05-12','08:35:43' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '40.330','-0.2382025','0.000','0.0000000000','2006-05-12','15:00:00' '41.330','2.2354728','0.000','0.0000000000','2006-05-05','00:00:00' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '40.696','0.8814325','0.000','0.0000000000','2006-04-28','15:56:00' '41.000','1.635019','0.000','0.0000000000','2006-04-28','15:55:00' '38.380','0.8642144','38.400','0.9167752000','2006 -04-28','15:56:10' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23' From the set above I would like to retrieve the latest measurement per date but if I try to group by date (like below) I always get the first result instead of the latest result. SELECT r1, r1_dev, r2, r2_dev, date, time FROM output WHERE output.id_modality = '1' AND output.id_linac='2' GROUP BY output.date ASC ORDER BY output.date DESC 'r1','r1_dev','r2','r2_dev','date','time' '50.000','0.334449476181','37.470','0.0000364374', '2006-07-27','11:26:00' '39.650','3.7714458','38.020','-0.4945683000','2006-05-12','08:22:38' '39.220','2.8528707','0.000','0.0000000000','2006-05-05','00:00:00' '37.970','-0.2132824','38.510','1.2058597000','2006-04-28','15:49:23'
View Replies !
Extra Numbers Column For Sorted Query
I have a query which selects records based on ordering (datatype: FLOAT). However I would like a result in which the first record has an extra column value of 1, the second record an extra column value of 2, the third record an extra column value of 3 and so on. I know it has something to do with an auxiliar numbers table. But I dont recall how to build the query.
View Replies !
Retrieve Records
I have 2 tables: 1. Price table: with Price_Id(PK), Price 2 columns 2. Booking table: with Booking_Id(PK) ,Booking_Date,Price_Id(FK) now I want to retrieve all the records in price table that are not referenced by booking table (i.e. not booked).
View Replies !
How To Retrieve Records That Differ By One Field Value?
I was wondering if there's a way to retrieve records, from single a table, that have the same values for all fields except for a single field (for example the field "ID")..? Assuming I have the following table : ID | Name | Age --------------------- 1234 John 27 2345 Larry 22 3456 Micheal 23 7777 John 27 Then the query needs to retrieve the 1st and last record.
View Replies !
Datetime :: Retrieve Records For Today And Yesterday
I have a table with datetime field and having problems retrieving records added today or yesterday or 2 days ago to date: I need 3 queries: 1. retrieve records of today (regardless what time it's added) 2. retrieve records of yesterday (regardless what time it's added) 3. retrieve records which are added 2 days ago TO DATE (the day before yesterday, yesterday and today)
View Replies !
Retrieve Multiple Linked Records Efficiently
I have some records in database related to supply chain. e.g. N1 supplies item I1 to Node N2 is represented as N1 I1 N2. Now I want to find all parent nodes or say chain of nodes supplying to particular node in an efficient way. Can you please tell me how I can do it with efficient query. Currently it needs multiple queries & performance drops considerably.
View Replies !
The Most Efficient Manner To Update Multiple Rows
I have a table that holds information about people The table is quite large (300,000 rows) I need to write code that update many of the rows in the table Here is what I need to do Two of the fields that I store about each person are: location and country The location is some free text which the user can type in, while the country is the ISO country code, like "US", "ES" and so on I need go over all the records in the database, and for those that have a non-empty location field, to extract the value and to try to guess what country the user is from and to update the Country field accordingly My problem is that I might find that most of the records need updating, and this might lead to 100,00+ update statements I cannot use LOAD DATA as this is product database Is there a way to update multiple rows (each with a different value and condition in a single query?
View Replies !
Query: It's Possible To Retrieve Only Numbers From A Field?
How to retrieve only numbers from a field? select firstname, address from `acme`.`anytable` where idanytable=100; +-----------+--------------------+ | firstname | address | +-----------+--------------------+ | ERICKA | RUA B NR 50 AP 202 | +-----------+--------------------+ select firstname, onlynumbers(address) from `acme`.`anytable` where idanytable=100; +-----------+---------+ | firstname | address | +-----------+---------+ | ERICKA | 50202 | +-----------+---------+
View Replies !
Retrieve Data From 2 Tables In 1 Query
I've got 2 tables content & sections I need to: SELECT id, title, section_id , status FROM content and now I'd like to get title from sections WHERE id = section_id from the first part of the query. I've tried with unions but doesn't seem to like it..
View Replies !
Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'") set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'") Is there any way to do this in one SQL query?
View Replies !
1 Record To Multiple Records
I'm looking for an efficient way of doing the following... table1 - groupName, firstGuy, secondGuy, thirdGuy,... sample - 'Red', 2, 6, 3,..... table2 - guyId, firstName, lastName sample - 2, 'Jim', 'Smith' ; 3, 'Bill', 'Adams' ; 6, 'Tim', 'Jones' If I queried groupName = 'Red', how do I get the result to be the names from table2. Subqueries?result to contain: red, Jim Smith, Bill Adams, Tim Jones I hope this is clear.
View Replies !
Query To Search And Retrieve File For Download
I am new to web development and have this challenge. I want to design a dynamic website for a college community using PHP and MYSQL. The objective of the site is to hold the annual report of all the departments such that visitors can select the desired department and the year of the report. The years could stretch back to as long as the college existed but I want to limit the backdate to 10 years for now. The visitor should be able to select a department and desired year, view the report online and also be able to download the same report stored in a directory in ms word or PDF format. What should the table structure look like? Also the PHP and SQL script to search for the stored files and make them available for download.
View Replies !
Can You Retrieve Information From Describe Table Query?
I was wondering if it is possible to extract the information from describe table to an application. So that a user may choose with which fields of a given table he wants to perform a search. More specifically is there a query that will return me for example the fields for that table and their respective types, that may be forwarded to something like PHP, and print them to the user?
View Replies !
Check If A Record Exists In The Last 30 Records
I want to check if a record exists in the last 30 records that were added. For example, i have a table wich contains about 34000 records by now. But i want to check if the songID is in the last 30 records. Now i select the last 30 records and put them in an array, and let php check if this songID is in the array. But it must be much easier if mysql this does!
View Replies !
Storing Many Records Associated To Single Record
Let's say that you have a table of car models and each model is associated to a make in another table through a foreign key. Now, in another table you need to store all of the models that the customer has selected. Is it correct to store them in a table like this (periods used for spacing): user_id, model_id ...1........1 ...1........2 ...1........4 ...2........2 ...2........23 ...3........9 etc. or is there a way to serialize this data that's easily searchable like this? user_id, models ...1.....1,2,4 ...2......2,23 ...3.......9 etc. Which is considered the best practice? I need to be able to quickly search for all people who have selected model_id "2" or model_id "23".
View Replies !
Finding Records That Are *similar* To Your Record
I want to enable users to create groups in my website. However; i also want to check that lots of users dont go and create lots of similar groups because they are unaware that other groups exist. So: i need to be able to find groups in my database which have names (if any) which are similar to the one that the user has entered in. Is there any existing MySQL function for doing this? One thing which i have found is the liebenstein distance, but it appears that you have to install that function as a user function or something...which seems a little beyond me right now. Is there perhaps another way of finding the similarity between two strings?
View Replies !
Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to get a resultset of previous and current and next record when the resultset is sorted by id and id=10. The table data is shown in the following: id Name Price 1 : : 5 : :<--------------previous record 10 : :<--------------id=10 12 : :<--------------next record 14 : : How do I write the sql statement if I do not know 5 and 12 are the previous and next id respectively?
View Replies !
Creating Child Records For Each Parent Record
I want to iterate through a parent table and then create a child record for each. Something like this (pseudocode): select * from parent; //Then for each parent I want to create a child and insert the parent vlaue insert into child(parentId, someField) values(parent.id, "XYZ");
View Replies !
Selecting Values For This Record Based On Other Records
Is it possible in a select to reference the contents of a column in a different record to the one currently being assessed? Kind of like :- Select id,prev_id,title,flag FROM my_table WHERE flag != "S" AND flag(prev_id)="S"; What I'd like it to do is select records which have flag set "S", but the record referred to in prev_id do not have flag set "S". If it matter, I'm stuck on 4.0.
View Replies !
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
View Replies !
How To Cascade Delete Related Records Without Deleting The Main Record?
Let's say I have the following database tables: CREATE TABLE user ( id INT UNSIGNED NOT NULL PRIMARY KEY, username VARCHAR(20) NOT NULL, ) ENGINE = INNODB; CREATE TABLE user_email ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, email VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; CREATE TABLE user_phone ( user_id INT UNSIGNED NOT NULL PRIMARY KEY, phone VARCHAR(100) NOT NULL, FOREIGN KEY (user_id) REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE = INNODB; What I want to do is delete all records in the 'user_email' and 'user_phone' tables without deleting the record in the user table. I have a database without nearly 20 tables with one-to-one and one-to-many relationships with the user record and I need a way to cascade delete records in relationship tables.
View Replies !
Sorted Result
this my query: $sql = "SELECT * FROM aims_technicallogs WHERE aircraftid = $dv_acftid ORDER BY tcl_flyingdate, tcl_datetime DESC"; i try execute, it run without error. but problem was the results not sorted. what the problem is? tcl_flyingdate = date format tcl_datetime = date and time format i m using mysql database version 4
View Replies !
Keeping Data Sorted
To followup my last post, how do I keep a field in a table already sorted. eg. if a field in it going to have values like "10", "3330" and "1", I want the row with the "1' first in the table, and the "10" second and so on. I want this to happen when I UPDATE, *not* when SELECT-ing, as, according to the last post, if I 'm doing a SELECT num where (num>1 AND num<2000) AND bar = "Toyota", by the time it gets past the "10" row, it should know not to go any further down the list looking for bar="Toyota".
View Replies !
Getting A Range Of Sorted Data
How to get a a range of sorted data. If I only want the 900.-1000. row of the sorted rows, then how can I do this? SELECT * FROM data ORDER BY age DESC LIMIT 1000 and then with a while() cycle I get the 900. - 1000. rows. That seems pointless, because the 1. - 800. rows just waste the memory.
View Replies !
Displaying Sorted Averages
Given a table with columns LotID, ItemName, CostPerUnit and UnitsSold, how would I produce a sorted list of the average of ( CostPerUnit * UnitsSold ) for all items with the same ItemName. Example LotID ItemName CostPerUnit UnitsSold 0 Ford 100 15 1 Chevy 75 10 2 Toyota 70 20 3 Ford 95 25 4 Ford 110 10 5 Chevy 80 20 6 Toyota 65 15 I'm looking for an output of Ford 1658 Toyota 1187 Chevy 1175 Which is created by Ford -> (100*15+95*25+110*10)/3 Toyota -> (70*20+65*15)/2 Chevy -> (75*10+80*20)/2
View Replies !
Insert In Sorted Order
Recently, i find that when i insert a record to a table it would insert the new record into table in a sorted order for example the table using field a as the sorting key New added record: 'Field A =c, FieldB=k' Existing records in the table: Field A| Field B -------------------------- a | x b | a d | z Record of table after insertion Field A| Field B -------------------------- a | x b | a c | k d | z i use the statement 'SELECT * FROM tablename' ***without using any 'order by'
View Replies !
Query- Get Every X Record?
My app loads my database every 30 seconds with weather data from several different weather stations (2880 records per station per day). I need to extract every 10th record for a station (288 records per day) to build a PHP array for a graph.
View Replies !
Select A List Sorted By Date
i am looking for way to get DATE() results properly from my database sorted by MONTH and DAY without YEAR. example: 2007-06-23 2003-03-01 2005-07-27 2006-11-28 2007-12-01 today is 2007-04-25 <? $query="SELECT * FROM domain_status ORDER BY (MONTH(date) < MONTH(NOW())), MONTH(date) ASC, DAY(date) ASC"; ?> the result is: 2007-04-20 <!-- this one is > 04-25 and therefor has be at the end of the row 2006-04-07 <!-- this one is > 04-25 and therefor has be at the end of the row 2007-06-30 2003-06-23 <-- this one must be on top of 06-30, cos its sooner.
View Replies !
Query Works With One Record
This query works great if there is one matching record in the Albums table. When there's more than one matching record it returns nothing. Code: SELECT Artists.Artist, Albums.Album_Name FROM Artists, Albums WHERE SOUNDEX('colname') = SOUNDEX(Artists.Artist) AND Albums.Artist_ID=Artists.Id
View Replies !
Returning Multi Sorted Result Sets
I have a table and I want to return query results sorted on two different fields, but am getting erroneous results because MySQL is sorting numeric fields as if they are strings. For example say there is a simple table with three fields, name, race and and age where race and age are defined as int(4). If I perform the following query, select * from person order by race, age; I might get results similar to the following mary, 1, 1 joe, 1, 10 bill, 1, 11, jeff, 1, 12, biff, 1, 2 leo, 1, 3, etc... I tried coercing the numeric fields with the binary and cast located here, but no luck. select * from person order by race, binary age; select * from person order by race, cast(age as binary);
View Replies !
Query Help: Get Oldest Record W/ Grouping?
I'm building a system that allows a maximum of two records per unit. When a new record is created for that unit, if there are already two records the oldest record is deleted. Thus, I'm trying to build a query that gets the oldest record for each unit, but also returns the number of records per unit. The trouble I'm having is in making sure the record I'm getting is the oldest one! I thought I could use the Order By clause to get the oldest record, but it doesn't work. In a database table where there are a total of four records: recordID unitID recordFile recordTime 10 1 someFile1.txt 1179778828 11 3 someFile5.txt 1179778828 12 3 someFile5.txt 1179778990 13 1 someFile5.txt 1179778956 The following query: SELECT unitID, COUNT(recordID) AS numRecords, recordID AS oldestID, recordFile, recordTime FROM records GROUP BY unitID ORDER BY recordTime DESC Returns the following result: unitID numRecords oldestID recordFile recordTime 3 2 12 someFile5.txt 1179778990 1 2 13 someFile5.txt 1179778956 These are the newest records, not the oldest. I'm not even sure that is a reliable case. I get the exact same result using the same query but with ASC instead of DESC!
View Replies !
Return Info On Last Record With One Query
Lets call the table: comics Lets call the fields I am looking for: ID filename I want to get the maximum ID from the table and the filename field corresponding to that maximum id. I can do it easy in two queries: select max(ID) as max_id from comics then in php send select filename from comics where ID=$id (where $id is the number returned from the first query) I would like to do ONE query like: Code: select max(ID) as max_id, and filename from comics where ID=max_id
View Replies !
Insert A Record Using MySQL Query Browser
I know I can probably type in the actual query, but I was wondering if there was a means of inserting a record without typing "insert into table_name (blah, blah2) values (blah, blah)". I'm looking for a method as easy as typing into an Excel spreadsheet or Access DB. (I know I'm gonna get bashed for mentioning Microsoft products.)
View Replies !
Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script, my table having the details of uploading time, file name, & uploader name. i need to find the recent uploaded file for all uploaders. i tried with this query, select file_name,uploded_by,MAX(date_time) from upload group by uploded_by; it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.
View Replies !
Finding All Records In One Table That Don't Have A Corresponding Record In Another Table
I have photo information stored in a table called pictures. One column is keyword. Visitors to my site can search for photos by keyword. I record search terms in another table called searches. Each search term is recorded, meaning there are duplicate search rows. This allows me to see easily the popularity of a search term. Step 1. What query can I run to find all search terms that return no matching picture in the picture table. I tried the following, but no luck:Quote:select term from searches left join pictures on term like keyword where picture_id is NULL
View Replies !
|
TRACKER
