How to Remove Certain Results from My Search Count

How to remove certain results from my search count?

You want AND, not OR:

where lower(person) not like '%b%' and
      lower(person) not like '%c%' and
      lower(person) not like '%e%' and
      lower(person) not like '%f%'  

This is logically equivalent to:

where not (lower(person) like '%b%' or
           lower(person) not like '%c%' or
           lower(person) not like '%e%' or
           lower(person) not like '%f%' 
          ) 

Or more concisely:

 where not regexp_contains(person, '[BbCcEeFf]')

Or if you want to be a bit inscrutable:

where not regexp_contains(person, '(?i)[bcef]')

The (?i) makes the pattern matching case-insensitive.

Update the count of searched results and change the selection based on the number of time user searched in array of objects

You can use refer following code

    const testData = [
  {
    id: 1,
    label: 'parent1',
    item: [
      {
        id: 21,
        label: 'child1',
        item: [
          {
            id: 211,
            label: 'child31',
            item: [
              {
                id: 2111,
                label: 'child2211',
                item: [{ id: 21111, label: 'child22111' }]
              }
            ]
          },
          { id: 222, label: 'child32' }
        ]
      },
      {
        id: 22,
        label: 'child2',
        item: [
          {
            id: 221,
            label: 'child421',
            item: [{ id: 2211, label: 'child2211' }]
          },
          { id: 222, label: 'child222' }
        ]
      }
    ]
  },
  {
    id: 2,
    label: 'parent2',
    item: [
      {
        id: 21,
        label: 'child2',
        item: [
          {
            id: 511,
            label: 'child51',
            item: [
              {
                id: 5111,
                label: 'child5211',
                item: [{ id: 51111, label: 'child52111' }]
              }
            ]
          },
          { id: 522, label: 'child352' }
        ]
      }
    ]
  }
];

// flatten down tree to array and add parent pointer
const flatten = (data) => {
  let flattenData = [data]
  if (data.item) {
    for (const item of data.item) {
      item.parent = data;
      flattenData = flattenData.concat(flatten(item));
    }
  }
  return flattenData;
}

let flattenData = [];

// flatten down the test data
for (const data of testData) {
  flattenData = flattenData.concat(flatten(data));
}

// to update showTree flag
const toggle = (item, expand = true) => {
  const parent = item.parent;
  if (parent) {
    parent.showTree = expand;
    if (parent.parent) {
      return toggle(parent, expand);
    }
    return parent;
  }
  return item;
}

/**
 * 
 * @param {targetLabel} query 
 * @returns function navigate with param forward flag
 */
const seach = (query) => {
  let index = -1;
  const items = flattenData.filter(x => x.label.includes(query));
  return (forward = true) => {
    if (index > -1) {
      items[index].selected = false;
      toggle(items[index], false);
    }
    index = index + (forward ? 1 : -1);
    let item = null;
    if (index > -1 && index < items.length) {
      items[index].selected = true;
      item = toggle(items[index], true);
    }
    return {
      item,
      index,
      length: items.length
    }
  }
}

const navigate = seach('child5211');

// next result
let result = navigate();

// previous result
result = navigate(false);

// result will look like this
/**
 * {
 *  item: root of current item with showTree and selected falgs or null if out of bound,
 *  index: current match,
 *  length: total match
 * }
 * 
 */

Delete all results having count(*)=1 in mysql

like this? you mean?
sqlFiddle example

MYSQL delete all results having count(*)=1

DELETE  si
FROM    t_session si
JOIN    (
        SELECT  sesskey
        FROM    t_session so
        GROUP BY
                sesskey
        HAVING  COUNT(*) = 1
        ) q
ON      q.sesskey = si.sesskey

You need to have a join here. Using a correlated subquery won't work.

See this article in my blog for more detail:

• Keeping rows

Remove all occurrences of a value from a list?

Functional approach:

Python 3.x

>>> x = [1,2,3,2,2,2,3,4]
>>> list(filter((2).__ne__, x))
[1, 3, 3, 4]

or

>>> x = [1,2,3,2,2,2,3,4]
>>> list(filter(lambda a: a != 2, x))
[1, 3, 3, 4]

Python 2.x

>>> x = [1,2,3,2,2,2,3,4]
>>> filter(lambda a: a != 2, x)
[1, 3, 3, 4]

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id ,
 date
HAVING
COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no , 
    date,
        COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no , 
            zip, 
            date
         FROM
            payment 
    
        ) 
        payment
 GROUP BY
    
    user_id,
    account_no , 
    
    date
HAVING COUNT(*) > 1

Select Count(*) returning old value from before deleting all the entries in a data table

I'll answer this myself just in case anyone else encounters this issue in the future, indeed as the commenters have said, you have to COMMIT everything you do on SQLdeveloper before expecting to see any actual changes in the database, just as if it was an actual SQL Transaction.

Thanks to the people that commented on this.

SQL Server: count appearance of each item in select results

You can do this in a query, you don't have to use a stored procedure. If I understand you correctly, you can use "group by" to solve the problem.

SELECT      url,
            count(*)
FROM        QueueLog
WHERE       logStatus = 'New'
AND         region = 'US'
AND         (
            flag = 'flag1' 
            OR 
            flag = 'flag2'
            )
GROUP BY url;

If you want to get only the urls that have duplicates, you can add a having:

SELECT      url,
            count(*)
FROM        QueueLog
WHERE       logStatus = 'New'
AND         region = 'US'
AND         (
            flag = 'flag1' 
            OR 
            flag = 'flag2'
            )
GROUP BY url
HAVING count(*) > 1;

My favorite way to delete duplicates involves using windowing functions. Either way, to delete duplicates you have to know which duplicate you want to delete. I'm assuming you want to delete the one with the newer dateEsc. This query here (or something like it) should give you all of the duplicate rows. After you've verified that they're right, it's not hard to change it from a select to a delete.

SELECT * FROM 
(
SELECT      EID,
            dateEsc,
            url,
            rank() OVER(PARTITION BY url ORDER BY dateEsc) as rank
FROM        QueueLog
WHERE       logStatus = 'New'
AND         region = 'US'
AND         (
            flag = 'flag1' 
            OR 
            flag = 'flag2'
            )
) a
WHERE a.rank > 1;

Basically, the inner query takes all rows with the same url and gives them a rank based the dateEsc. So the one with the oldest dateEsc would get a "1" in the rank column, the next oldest would get the rank 2, and so on. Then we know we want to keep the one with rank 1-- the duplicates will be anything with rank 2 or higher. So we select those rows in the outer query. If you want to change entry is the "correct one", just change rank() OVER(PARTITION BY url ORDER BY dateEsc) as rank to rank() OVER(PARTITION BY url ORDER BY EID) as rank
or such.

How to remove item from list in C#?

List<T> has two methods you can use.

RemoveAt(int index) can be used if you know the index of the item. For example:

resultlist.RemoveAt(1);

Or you can use Remove(T item):

var itemToRemove = resultlist.Single(r => r.Id == 2);
resultList.Remove(itemToRemove);

When you are not sure the item really exists you can use SingleOrDefault. SingleOrDefault will return null if there is no item (Single will throw an exception when it can't find the item). Both will throw when there is a duplicate value (two items with the same id).

var itemToRemove = resultlist.SingleOrDefault(r => r.Id == 2);
if (itemToRemove != null)
    resultList.Remove(itemToRemove);

---

Related Topics