I have a database table where there is a column hostname and few other columns. in hostname, there are many instances of rows with same hostnames.  Ex. 192.0.0.1 has 40 entries and 192.0.0.2 has 35 entries and so on.  Is it possible to get the list of latest entries for each hostname? meaning in the result i should get  one latest for 192.0.0.1 and one latest for  192.0.0.2 and so on. I tried with SELECT host,cluster,region,service    FROM system  WHERE host IN (&quot;171.33.64.158&quot;,&quot;171.33.64.159&quot;)    ORDER BY id DESC LIMIT 1;   but the output contains only one row which is probably the first row of the entire result. Can anyone please help me in this requirement? Thanks, Swapnil
13936
Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select *  from sometable order by name limit 20,10  to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically. In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this: select *  from sometable where rownum &lt;= 10 order by name  will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.
12604
I have 2 tables. One (domains) has domain ids, and domain names (dom_id, dom_url).  the other contains actual data, 2 of which columns require a TO and FROM domain names. So I have 2 columns rev_dom_from and rev_dom_for, both of which store the domain name id, from the domains table.  Simple.  Now I need to actually display both domain names on the webpage. I know how to display one or the other, via the LEFT JOIN domains ON reviews.rev_dom_for = domains.dom_url query, and then you echo out the dom_url, which would echo out the domain name in the rev_dom_for column.  But how would I make it echo out the 2nd domain name, in the dom_rev_from column?
8230
I often need to select a number of rows from each group in a result set.  For example, I might want to list the 'n' highest or lowest recent order values per customer.  In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). This part is definitely optional/for extra credit and not intended to dissuade people from answering.  What are the main options for solving these types of problems in SQL Server 2005 and later? What are the main advantages and disadvantages of each method?  AdventureWorks examples (for clarity, optional)   List the five most recent recent transaction dates and IDs from the TransactionHistory table, for each product that starts with a letter from M to R inclusive. Same again, but with n history lines per product, where n is five times the DaysToManufacture Product attribute. Same, for the special case where exactly one history line per product is required (the single most recent entry by TransactionDate, tie-break on TransactionID.
74018
I am storing the response to various rpc calls in a mysql table with the following fields:  Table: rpc_responses  timestamp   (date) method      (varchar) id          (varchar) response    (mediumtext)  PRIMARY KEY(timestamp,method,id)   What is the best method of selecting the most recent responses for all existing combinations of method and id?   For each date there can only be one response for a given method/id. Not all call combinations are necessarily present for a given date. There are dozens of methods, thousands of ids and at least 365 different dates    Sample data:  timestamp  method  id response 2009-01-10 getThud 16 "....." 2009-01-10 getFoo  12 "....." 2009-01-10 getBar  12 "....." 2009-01-11 getFoo  12 "....." 2009-01-11 getBar  16 "....."   Desired result:  2009-01-10 getThud 16 "....." 2009-01-10 getBar 12 "....." 2009-01-11 getFoo 12 "....." 2009-01-11 getBar 16 "....."   (I don't think  is the same question - it won't give me the most recent response)
87358
Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This proved somewhat difficult because they could simply re-run their query seemingly endlessly and it would always return the same set of rows in the same order.  I have noticed this before and all I could really do is insist that they trust me and not simply assume that a database table will behave like a traditional CSV or Excel file.  For example, executing the (PostgreSQL) query  create table mytable (     id INTEGER PRIMARY KEY,     data TEXT ); INSERT INTO mytable VALUES     (0, 'a'),     (1, 'b'),     (2, 'c'),     (3, 'd'),     (4, 'e'),     (5, 'f'),     (6, 'g'),     (7, 'h'),     (8, 'i'),     (9, 'j');   will create a table with a clear conceptual order. Selecting that same data in the simplest way would be:  SELECT * FROM mytable;   Always gives me the following results:   id | data  ----+------   0 | a   1 | b   2 | c   3 | d   4 | e   5 | f   6 | g   7 | h   8 | i   9 | j (10 rows)   I can do this over and over again and it will always return to me the same data in the same order. However I know that this implicit order can be broken, I have seen it before, particularly in large datasets, where some random value will get apparently thrown up into the "wrong" place when selected. But it has occurred to me that I don't know how this happens or how to reproduce it. I find it difficult to get results on Google because the search query tends to just return general help on sorting result sets.  So, my questions are essentially these:   How can I demonstrably and concretely prove that the return order of rows from a query without an ORDER BY statement is not reliable, preferably by causing and showing a breakdown of the implicit order even when the table in question is not updated or edited? Does it make any difference at all if the data is only inserted once en masse and then never updated again?   I would prefer a postgres-based answer since that is the one I am most familiar with but I'm more interested in the theory itself.
85566
I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales that are unique based on day and price will get updated to an active status.  So I'm thinking:  UPDATE sales SET status = 'ACTIVE' WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)              FROM sales              HAVING count = 1)   But my brain hurts going any farther than that.
15483
I'm experiencing an annoying problem with a table named SysMessages in one of our databases. When I run a select statement I don't get the contents of this table, but of the sys.sysmessages view in the master database. A use [DBNAME] doesn't help, nor select * from [DBNAME].dbo.SysMessages.  I'm in the middle of a migration to a newer SQL Server version. In SQL Server 2008 R2 I didn't encounter this problem with this database, but in SQL Server 2016 I'm not able to query my own SysMessages table.  Any help would be greatly appreciated.
36202
I am attempting to get a list of "last logon time" of "all users" on Windows Server 2012, but currently only know of how to list a single user login, which is:  net user username | findstr /B /C:"Last logon"  Any ideas?  *P.S. This servers only purpose is to host the RDP connections; not tied to a domain/AD.
47381
When an insert statement is executed one or more rows is inserted into the table, is there any way to extract the last inserted row in SQL Server?
30328
The base problem I'm facing is that I need the latest record for each item.    A little setup... MySQL 5.6.14.  I need to create two views (because MySQL won't let me have a subquery in a view).  My first query sets up the data like this.  select      `inventoryrecords`.`inventoryrecordid` AS `inventoryrecordid`,     `inventoryrecords`.`logicaldeviceid` AS `logicaldeviceid`,     `inventoryrecords`.`passrfid` AS `passrfid`,     `inventoryrecords`.`tagepc` AS `tagepc`,     `inventoryrecords`.`currentstate` AS `currentstate`,     `inventoryrecords`.`statedateutc` AS `statedateutc`,     `inventoryrecords`.`ownerobjectid` AS `ownerobjectid`,     `inventoryrecords`.`ownerobjecttype` AS `ownerobjecttype` from     `inventoryrecords` where     1 order by `inventoryrecords`.`statedateutc` desc   Then I can restrict everything to just the last record for each TagEPC with my "real" query like this.  select      `lastinventoryrecords_step1`.`inventoryrecordid` AS `inventoryrecordid`,     `lastinventoryrecords_step1`.`logicaldeviceid` AS `logicaldeviceid`,     `lastinventoryrecords_step1`.`passrfid` AS `passrfid`,     `lastinventoryrecords_step1`.`tagepc` AS `tagepc`,     `lastinventoryrecords_step1`.`currentstate` AS `currentstate`,     `lastinventoryrecords_step1`.`statedateutc` AS `statedateutc`,     `lastinventoryrecords_step1`.`ownerobjectid` AS `ownerobjectid`,     `lastinventoryrecords_step1`.`ownerobjecttype` AS `ownerobjecttype` from     `lastinventoryrecords_step1` group by `lastinventoryrecords_step1`.`tagepc` order by `lastinventoryrecords_step1`.`statedateutc` desc   When I try to select * from the "real" view, I do not get the data I expect.  However when I run the query with the sub-query in a window I do.  select * from (  lastinventoryrecords_step1 ) group by tagepc order by statedateutc desc   yields incorrect data whereas   select * from (     select          inventoryrecordid,         logicaldeviceid,         passrfid,         tagepc,         currentstate,         statedateutc,         ownerobjectid,         ownerobjecttype     from         inventoryrecords     where         1     order by statedateutc desc ) as t1 group by tagepc order by statedateutc desc   yields the right data.  What in the heck is going on?  What am I missing?
120659
Say I have the following query:  SELECT *  FROM AppDetails, AppTags  WHERE AppDetails.AppID = '1'    AND AppDetails.AppID = AppTags.AppID   Which gives the following results:  AppID    AppName        AppType    Tag 1        Application1   Utility    Test1 1        Application1   Utility    Test2 1        Application1   Utility    Test3   How can I modify the query to return something like this:  AppID    AppName        AppType    Tags 1        Application1   Utility    Test1,Test2,Test3
20935
I want to return top 10 records from each section in one query. Can anyone help with how to do it?  Section is one of the columns in the table.  Database is SQL Server 2005. I want to return the top 10 by date entered.  Sections are business, local, and feature.  For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.
28961
Table #01 Status:  StatusID    Status -----------------------  1          Opened  2          Closed  3          ReOpened  4          Pending   Table #02 Claims:  ClaimID     CompanyName StatusID -------------------------------------- 1               ABC     1 2               ABC     1 3               ABC     2 4               ABC     4 5               XYZ     1 6               XYZ     1   Expected Result:  CompanyName TotalOpenClaims TotalClosedClaims TotalReOpenedClaims TotalPendingClaims -------------------------------------------------------------------------------- ABC                 2           1                      0               1 XYZ                 2           0                      0               0   How do I need to write the query so that I could get the result as expected?
119927
How do I page results in SQL Server 2005?  I tried it in SQL Server 2000, but there was no reliable way to do this. I'm now wondering if SQL Server 2005 has any built in method?  What I mean by paging is, for example, if I list users by their username, I want to be able to only return the first 10 records, then the next 10 records and so on.  Any help would be much appreciated.
81999
What's the most efficient way to select the last n number of rows in a table using mySQL? The table contains millions of rows, and at any given time I don't know how large the table is (it is constantly growing). The table does have a column that is automatically incremented and used as a unique identifier for each row.
58142
I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.  CustomerID, CustomerName, Vehicle 1, Dan, Mazda 1, Dan, Suzuki 2, John, Ford 3, Dasha, Volvo 3, Dasha, Ford   Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.  1, Dan, Mazda+Suzuki 2, John, Ford 3, Dasha, Volvo+Ford   Thanks
61610
I have a query which selects a max value over all the values of in a datetime column (dtimesent).  select dtimesent, max(ResponseTime) AS maxtime FROM allrequests  where Request like '%Requestsa%' group by dtimesent   I need to get the value of the request colmn for each max response time in each group by. Is there any easy way to do this?  There can be multiple rows per dtimesent value so I can't just include the request column in the group by,  Many thanks,